Post reply

problem selecting varchar value

  • July 9, 2009 at 12:12 pm

    #1022588

    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 post 1 (of 2 total)

You must be logged in to reply to this topic. Login to reply