extact last 3 characters from a string

Question

extact last 3 characters from a string

Viewing 9 posts - 16 through 23 (of 23 total)

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

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

ps. (6/17/2009)
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'.

I think all the ones after this one are just academic, they show different ways to accomplish the same thing, but THIS is the easiest AND the one he should use. He wants the last three characters.. Bang.. Done.

CEWII

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

my code was in response to this question:

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

😎

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 3

SALIM ALI (6/18/2009)
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 ) )

Heh... Old post I know but I just have to ask... why would you do two relatively expensive "reverses" when RIGHT will do the job just fine? (I mean before this thread got "silly". )

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rick Quayle SSC Enthusiast Points: 102 More actions · Answer 4

If you wish to remove the last 3 characters you can do this in the select statement without modifing the table contents (select reverse(stuff(reverse(field_name),1,3,'fig'))Field_Name

I do this to rename all the uploads from jpg,doc,eml,mpg, etc for all files so i can show a gif file as the thumbnail before they view the full picture or document.

Hope this helps someone other than myself.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

That will certainly work and if you don't have many rows, you probably won't notice, but REVERSE is comparatively very expensive... it would be computationally cheaper if you just used STUFF and LEN alone by a fair bit.

SELECT STUFF(yourcolumn,LEN(yourcolumn-3),3,'GIF') which would probably be the fastest.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steve woodward-1090073 SSC Enthusiast Points: 124 More actions · Answer 6

Another alternative to the last request would be

select stuff(replace(@string,' ',''),len(replace(@string,' ','')) - 2,0,' ')

Rick Quayle SSC Enthusiast Points: 102 More actions · Answer 7

My statement for testing yours was (SELECT STUFF(IName,LEN(IName-3),3,'GIF') from dbo.MImage2)

this gives an error

(Server: Msg 245, Level 16, State 1, Line 1

Syntax error converting the varchar value '07072704.JPG' to a column of data type int.)

I am taking the .xxx for file names and replacing all that is pulled with GIF

With this error i get when attempting to use your solution, could not do what you suggest.

My solution without having to declare items or use Len- (select reverse(stuff(reverse(field_name),1,3,'fig'))Field_Name

The overhead for this above is nothing in my case as this is only done when the user views a message that contains pictures, documents etc.

Example of select statement use

("select IName,reverse(stuff(reverse(Iname),1,3,'fig'))IName1,

IDes,IDirect,Owner,Sb,DUL,rn, pic from mimage2")

The IName1 as shown is an added field just for the thumbnail view section of the HTML output to use. within ("<IMG src='<%#"Pmags/"& Dpd1.text & "/V1_" & server.UrlEncode(Container.DataItem("IName1"))%>'>") I have tested this with messages that contain over 100 jpg,

doc etc. files and it took less then 2 seconds to process the reversed file name within the select statement.

Prometheus-867888 SSC Enthusiast Points: 170 More actions · Answer 8

Hi Charu,

Bit of a SQL Server "newbie" myself, but from years of Access work, how about using InStr(column_name, " ") to find your space, with a combination of Left() and Right ()s to combine your field contents into one string, then using the previously posted methods to separate the left (n) chars from the right 3?.

Just a thought,

Sid.G.

dandanaveenreddy SSC Rookie Points: 43 More actions · Answer 9

substring(data,-3) will work in some databases like my sql so you can use there