Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

extact last 3 characters from a string Expand / Collapse
Author
Message
Posted Wednesday, June 17, 2009 12:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, June 17, 2009 12:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 7, 2014 6:44 PM
Points: 1,989, Visits: 864
Have a look in Books Online regarding "String Functions". There's one there that does exactly what you want.
Post #736242
Posted Wednesday, June 17, 2009 12:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 6, 2014 3:07 PM
Points: 2,242, Visits: 3,643
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
Posted Thursday, June 18, 2009 3:43 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:22 PM
Points: 352, Visits: 173
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
Posted Friday, June 19, 2009 10:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:08 PM
Points: 136, Visits: 633
select substring ('ABC123',4,3)



-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
Post #738509
Posted Saturday, June 20, 2009 1:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 6, 2014 3:07 PM
Points: 2,242, Visits: 3,643
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
Posted Saturday, June 20, 2009 4:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 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

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
Posted Saturday, June 20, 2009 6:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 6, 2014 3:07 PM
Points: 2,242, Visits: 3,643
lol, lots of ideas to get last few characters from a string.




Pradeep Singh
Post #738809
Posted Wednesday, October 14, 2009 4:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, October 14, 2009 4:44 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:22 PM
Points: 352, Visits: 173
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse