Select last 4 characters from column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select last 4 characters from column

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-11-27 : 05:14:10

Hi all,
I have a column in SQL table that has data like
00MMTT12STORE1
00MMTT12STORE2

I need a select statement that only returns STORE1,STORE2
IE: DO NOT RETURN first 8 characters..

Thx in advance.
Ray..

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-11-27 : 05:28:30

Hi, Try this

SELECT LEFT(COLUMN,6) FROM table

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-11-27 : 05:29:44

Or Try this

select substring(columnname,9,len(columnname)) from table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 06:03:58

quote:
Originally posted by rwaldron

Hi all,
I have a column in SQL table that has data like
00MMTT12STORE1
00MMTT12STORE2

I need a select statement that only returns STORE1,STORE2
IE: DO NOT RETURN first 8 characters..

Thx in advance.
Ray..

Your explanation and topic subject seems contradicting

Madhivanan

Failing to plan is Planning to fail

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-11-27 : 06:06:36

Thx Guys,
select substring(columnname,9,len(columnname)) from table
did the trick..

Ray..
just a note that
SELECT LEFT(COLUMN,6) FROM table
retunrns first 6 chars but this si what I wanted removed

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-11-27 : 06:45:53

Just to let you know that I used a combination of both answers to do what I needed.
ie:i wanted to remove first 8 characters and read the next 5 chars.
so iwa nted from char 9-13 stripping the begining and the end.
left ( substring(columnname,9,len(columnname)) ,5 )

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-27 : 06:56:09

per the description in the post title...
select right(mycol,4) from mytable.
or
select right(rtrim(mycol),4) from mytable if trailing spaces not wanted.

NOTE
re other code given above, for a field defined as ( say)20 long,
select substring(columnname,9,len(columnname)) from table
will ask for
select substring (columnname, 9, 20) from table
which in english is "give me 20 characters of data starting at the 9th position of columnname"
given that 29 is longer than the length of the source field this may not work outside of the current implementation...if porting, or upgrading, or changeing field data types.

while it may work at the moment, don't bank on it forever.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources