Retrieving first 100 chars from a column of type text
-
Hi,
I have a column, data type Text in one of the tables. Is that possible to retrieve only the first 100 characters from that column?
TAI.
-
Yes, just use
select substring([textfield],1,100)
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Or left(field, 100)
Brian
-
cannot use LEFT with text data type
Far away is close at hand in the images of elsewhere.
Anon. -
This example from books on line seems to give the answer?
Examples
This example reads the second through twenty-sixth characters of the pr_info column in the pub_info table.
USE pubsGODECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(pr_info) FROM pub_info pr INNER JOIN publishers p ON pr.pub_id = p.pub_id AND p.pub_name = 'New Moon Books'READTEXT pub_info.pr_info @ptrval 1 25GO -
Sorry about the formatting earlier, you need to see the READTEXT explanantion from BOL
USE pubs
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1 25
GO
-
Just remember that first 100 char should be expressed as:
READTEXT pub_info.pr_info @ptrval 0 100
where 0 is the first char and 100 is the length...
HTH
-
Thanks for your reply.
-
You could also cast the Text as Varchar, andreplace CR/LF by a space.
I use:
LTRIM(REPLACE(CAST([TextField] AS VARCHAR(100), CHAR(13) + CHAR(10), ' ')))
Sometimes it ends on a #13, in my solution this it not a problem, but you could raplace this single #13 by nothing.
I even have a ... on the end when the length is more then 100.
I use a (pseudo code):
+CASE WHEN LEN > 100 THEN '...' ELSE '' END
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply