extact last 3 characters from a string

Question

Post reply

extact last 3 characters from a string

shiwani2002sg

Ten Centuries

Points: 1037
More actions
June 17, 2009 at 12:01 am

#133007

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

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

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

Glenn Dorling SSCertifiable Points: 7876 More actions · Answer 1

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

ps. One Orange Chip Points: 29422 More actions · Answer 2

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

SALIM ALI SSC Eights! Points: 859 More actions · Answer 3

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 ) )

myheadhurts Ten Centuries Points: 1262 More actions · Answer 4

select substring ('ABC123',4,3)

-MarkO

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

ps. One Orange Chip Points: 29422 More actions · Answer 5

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

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('')

ps. One Orange Chip Points: 29422 More actions · Answer 7

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

Pradeep Singh

newbie_sql SSC Eights! Points: 954 More actions · Answer 8

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

SALIM ALI SSC Eights! Points: 859 More actions · Answer 9

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

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 10

Does this help?

DECLARE @STR NVARCHAR(10)

SET @STR = 'ABCDEFGHI'

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

Cheers,

J-F

newbie_sql SSC Eights! Points: 954 More actions · Answer 11

newbie_sql

SSC Eights!

Points: 954

October 14, 2009 at 4:50 am

#1065581

Many thanks

newbie_sql SSC Eights! Points: 954 More actions · Answer 12

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?

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 13

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

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 14

another method...

DECLARE @txt VARCHAR(20)

SELECT @txt = 'helloALL'

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

🙂