How to get substring from another string

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

General SQL Server Forums

New to SQL Server Programming

How to get substring from another string

Author

Topic

paha
Starting Member

3 Posts

Posted - 2011-01-11 : 03:03:13

Table has one column (col1) with data like :
aaa_10_morechars1
aaaa_111_muchmorechars
a_121234_bignothing_morechars

I need to show only the number in the middle from those strings.

This query gives me the start position of the number:
SELECT SUBSTRING(col1,CHARINDEX('_',col1)+1,LEN(col1)) FROM table
but my brain crashed after trying to get last number position.

paha
Starting Member

3 Posts

Posted - 2011-01-11 : 04:07:29

My solution :
SELECT SUBSTRING(
SUBSTRING(col1,CHARINDEX('_',col1)+1,LEN(col1)),
0,
CHARINDEX('_',SUBSTRING(col1,CHARINDEX('_',col1)+1,LEN(col1)))
)
FROM Table

Anyone got something shorter and simpler xD

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-11 : 04:39:28

not yet will let you know if got one ;)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-12 : 02:33:18

Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2010/04/22/extracting-numbers-part-2.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources