Substring - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Substring

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-02-12 : 09:10:04

Guys,

I have names in the following format, is there any way to strip the name and grab caaXXXXX from the name. I have tried substring but could develop the logic to grab the text after last place holder

caaxxxxx always occurs after the last place holder '_'

John_l_smith_caa12000
mike_caa12001
Mark_m_andrew_levey_caa12002

Any suggestions inputs would help.

Thanks

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-12 : 09:24:35

Hope this link helps to solve your problem.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96288

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-12 : 09:25:29

[code]SELECT data, [name] = right(data, CHARINDEX('_', REVERSE(data)) - 1)
FROM
(
SELECT data = 'John_l_smith_caa12000' UNION ALL
SELECT data = 'mike_caa12001' UNION ALL
SELECT data = 'Mark_m_andrew_levey_caa12002'
) d

/*
data name
---------------------------- ----------------------------
John_l_smith_caa12000 caa12000
mike_caa12001 caa12001
Mark_m_andrew_levey_caa12002 caa12002

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-12 : 09:27:50

[code]select substring(col, charindex('_caa', col)+1, 8)
from Table[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources