How to extract number from a string? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 extract number from a string?

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2011-06-02 : 05:33:30

I need to strip out the RAD_1011_CC_ from ColumnA and put the resulting number in Temp Column.

ColumnA Temp Column

RAD_1011_CC_656301 656301
RAD_1011_CC_657101 657101
RAD_1011_PL_169703
RAD_1011_CC_659301
RAD_1011_CC_659401
RAD_1011_CC_661401
RAD_1011_CC_663401
RAD_1011_CC_663901
RAD_1011_CC_664801
RAD_1011_CC_666001
RAD_1011_DU_86002

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-02 : 05:41:16

Select
ColumnA,
Temp = Stuff(ColumnA,1,12,'')
From YourTable

Corey

I Has Returned!!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-02 : 05:42:30

Try this

Declare @t Table (columnA nvarchar(55))
Insert into @t
Select 'RAD_1011_CC_656301' union all
Select 'RAD_1011_CC_657101' Union all
Select 'RAD_1011_PL_169703'

Select columnA,Substring(columnA,13,20) as Temp From @t

Select ColumnA,REVERSE(SUBSTRING(REVERSE(COLUMNA),0,CHARINDEX('_',reverse(ColumnA)))) from @t
--VAIBHAV

In Love... With Me!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-06-02 : 05:46:09

If length of string is not fix but it is sure that number will appear in last then try this -

SELECT columnA, REVERSE(LEFT(REVERSE(columnA),CHARINDEX('_',REVERSE(columnA)) - 1))

Vaibhav T

If I cant go back, I want to go fast...

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-06-02 : 06:04:38

Select columnA,PARSENAME(REPLACE(columnA,'_','.'),1) as Temp From @t

--------------------------
http://connectsql.blogspot.com/

insanepaul
Posting Yak Master

178 Posts

Posted - 2011-06-02 : 06:35:16

Thanks everyone for your quick response. I've tested all your ideas and have come up with a solution

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-02 : 07:28:12

What was the solution?

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources