July 9, 2009 at 3:32 am
hi
I have a varchar(50) column , but in the application the field is restricted to 15 characters. So, I have to return only 15 chars, but I don't have to truncate the result. An example to help looks like this :
declare @a varchar(50)
set @a = 'michael the greatest'
select substring(@a,0,15)
This script returns
michael the gr
But the result should be like
michael the
The last word should not apear if it is truncated.
Can you help me?
10q
July 9, 2009 at 12:12 pm
Hey there,
First a quick note : your example is actually 14 characters long. For 15 characters, it would be 'michael the gre'.
The idea is to look at the first space character from the end of the truncated string, and cut any character before that. Hence, I use Reverse to reverse the string, and CharIndex to get the space character index, specifying to start 15 characters from the end.
In the event where there is no space found, however, you can react in many different ways. I present you three ways : send the whole string, send an empty string, send a truncated string.
declare @a varchar(50)
set @a = 'michael the greatest'
SELECT
@a AS OriginalString,
LEFT(@a, 15) AS RoughCutString,
LTrim(LEFT(@a, Len(@a) - CharIndex(' ', Reverse(@a), Len(@a) - 15))) AS CleanWithWholeString,
LTrim(LEFT(@a, Len(@a) - IsNull(NullIf(CharIndex(' ', Reverse(@a), Len(@a) - 15), 0), Len(@a)))) AS CleanWithNoString,
LTrim(LEFT(@a, Len(@a) - IsNull(NullIf(CharIndex(' ', Reverse(@a), Len(@a) - 15), 0), Len(@a) - 15))) AS CleanWithRoughString
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply