Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

substring more than 4000 characters Expand / Collapse
Author
Message
Posted Tuesday, November 10, 2009 2:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 11:45 AM
Points: 16, Visits: 59
i have a data which has 8500 characters and i need the characters from 20 to 8350. how to do?
substring returns only 4000 characters.

please help
Post #816359
Posted Tuesday, November 10, 2009 4:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 31, 2010 1:28 AM
Points: 141, Visits: 263
select substring(column_name,20,8340) from table_name

is the above one is not working for you ?

Thanks,
Chandru

Post #816394
Posted Tuesday, November 10, 2009 7:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 8, 2014 7:12 AM
Points: 263, Visits: 443
This is what I use, except it's for text data types. I thought that varchar (if that's what your field is set as) is a max lenght of 4000. Anyway, here's my script to output text that is more than 4000 characters.

Declare @ptr varbinary(16),
@length int,
@Search varchar(200)

Set @Search = 'Title: 29aa58348'
--Set @Search = 'Title: 29aa58348'

Select @length = DataLength(FODocument)
From Admin..PrintJob
Where DocumentName = @Search

Select @ptr = TextPtr(FODocument)
From Admin..PrintJob
Where DocumentName = @Search

--Select @Search As [SearchParameter], @length As [LengthOfField], @ptr As [PointLocation]

If @ptr IS NOT NULL
ReadText PrintJob.FODocument @ptr 0 @length

Post #816526
Posted Tuesday, November 10, 2009 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 31, 2010 1:28 AM
Points: 141, Visits: 263
instead of going for varbinary or something you can use varchar(max).

Just try out that.

Thanks,
Chandru
Post #816538
Posted Tuesday, November 10, 2009 3:19 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, November 24, 2014 3:46 AM
Points: 709, Visits: 1,442
You can use the substring function with text data, so you will not have to deal with text pointers. The only thing is that instead of specifying the offset as the start and length characters, it needs to be specified in bytes.

http://msdn.microsoft.com/en-us/library/ms187748(SQL.90).aspx


Joie Andrew
"Since 1982"
Post #816885
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse