extact last 3 characters from a string

  • i wish to extract last 3 characters of a string, in sql server 2005,.

    substring doesnt accept -3 as length. so plz suggest some way

    ex helloALL

    output : ALL

  • Have a look in Books Online regarding "String Functions". There's one there that does exactly what you want.

  • shiwani2002sg (6/17/2009)


    i wish to extract last 3 characters of a string, in sql server 2005,.

    substring doesnt accept -3 as length. so plz suggest some way

    ex helloALL

    output : ALL

    Select RIGHT('helloALL',3) will give 'ALL'.



    Pradeep Singh

  • you could try this, it's a bit crude but works...

    declare @string varchar(50)

    SET @string = 'ABCDEFGHIJ123'

    select reverse ( substring ( reverse ( @string ) , 1 , 3 ) )

  • select substring ('ABC123',4,3)

    -MarkO

    "You do not really understand something until you can explain it to your grandmother" - Albert Einstein

  • select substring(srcString, charindex(targetString, SrcString),len(srcString)-charindex(targetString, SrcString)+1)

    This is a generic statement to be used in place of Right() where srcString is the main string and targetString is the string to be found from srcString.



    Pradeep Singh

  • Just for fun - if RIGHT and CHARINDEX is prohibited but you are allowed to use a Tally table 😀

    DECLARE @txt VARCHAR(20)

    SELECT @txt = 'helloALL'

    SELECT

    SUBSTRING(@txt, N, 1)

    FROM Tally

    WHERE N BETWEEN LEN(@txt) - 2 AND LEN(@txt)

    FOR XML PATH('')

  • lol, lots of ideas to get last few characters from a string.:-D



    Pradeep Singh

  • Hi,

    I have a slightly similar problem

    I hav a nvarchar field with no particular format of where spaces are

    I want to split the string as below

    <all chars-3> space <last 3 chars>

    Eg: "ABCDEFG" should appear as "ABCD EFG"

    "ABC DEFGH" should appear as "ABCDE FGH" etc

    How can i do this using string functions. Pls help. Thanks

  • how about...

    DECLARE @string VARCHAR(20)

    SELECT @string = 'ABC DEFGH'

    select @string = replace(@string,' ','')

    select @string = substring ( @string, 1,(len(@string)-3)) + ' ' +

    substring ( @string, (len(@string)-2),3 )

    select @string

  • Does this help?

    DECLARE @STR NVARCHAR(10)

    SET @STR = 'ABCDEFGHI'

    SELECT substring(@str, 1, len(@str) - 3) + ' ' + right(@str, 3)

    Cheers,

    J-F

  • Many thanks

  • thanks

    Im stuck at another thing now

    I have to do this conversion for all rows in the table

    if i write select @string=str_col from tbl1

    follwed by all the other string conversion statements;it gives me only the first row

    How do i write the select command in the stored procedure statement to run for all rows in the table?

  • Well then, don't use the variable, it was only for the demo. Use your column name instead, and select it directly from your table.

    Cheers,

    J-F

  • another method...

    DECLARE @txt VARCHAR(20)

    SELECT @txt = 'helloALL'

    select STUFF(@txt,LEN(@txt)-2,0,' ')

    🙂

Viewing 15 posts - 1 through 15 (of 23 total)

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