extact last 3 characters from a string

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » extact last 3 characters from a string

24 posts, Page 1 of 3

1 2 3 »»»

extact last 3 characters from a string

Rate Topic

Display Mode

Topic Options

Author

Message

shiwani2002sg

Posted Wednesday, June 17, 2009 12:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 17, 2009 3:41 AM
Points: 33, Visits: 97

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

Post #736233

Glenn Dorling

Posted Wednesday, June 17, 2009 12:23 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:46 PM
Points: 1,541, Visits: 832

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

Post #736242

ps.

Posted Wednesday, June 17, 2009 12:58 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

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

Post #736249

SALIM ALI

Posted Thursday, June 18, 2009 3:43 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 6:40 AM
Points: 351, Visits: 158

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

Post #737332

myheadhurts

Posted Friday, June 19, 2009 10:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 04, 2013 12:17 PM
Points: 135, Visits: 608

select substring ('ABC123',4,3)

-MarkO

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

Post #738509

ps.

Posted Saturday, June 20, 2009 1:49 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

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

Post #738759

Florian Reischl

Posted Saturday, June 20, 2009 4:43 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

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

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

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Post #738794

ps.

Posted Saturday, June 20, 2009 6:37 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

lol, lots of ideas to get last few characters from a string. BigGrin

Pradeep Singh

Post #738809

newbie_sql

Posted Wednesday, October 14, 2009 4:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 27, 2010 8:09 AM
Points: 28, Visits: 35

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

Post #802613

SALIM ALI

Posted Wednesday, October 14, 2009 4:44 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 6:40 AM
Points: 351, Visits: 158

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

Post #802626

« Prev Topic | Next Topic »

24 posts, Page 1 of 3

1 2 3 »»»

Permissions