extract only numbers from 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

SQL Server 2000 Forums

Transact-SQL (2000)

extract only numbers from string

Author

Topic

cray
Starting Member

9 Posts

Posted - 2005-11-03 : 12:48:12

I have a string field that is varchar 15, example:
AA AA# 9 AAaaa# 999999
The last 6 digits can be from 1 to 999999. I need to pull only the numeric digits from the end of this string. right(fieldname,6) doesn't work because it starts from the last character entered. Any ideas?

Kristen
Test

22859 Posts

Posted - 2005-11-03 : 13:03:58

Something like this perhaps?


DECLARE @strTemp varchar(1000)
SET @strTemp = 'AA AA# 9 AAaaa# 123456'
SELECT RIGHT(@strTemp, PATINDEX('%[^0-9]%', REVERSE(@strTemp))-1)

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-03 : 14:07:26

We did this a while ago...

http://weblogs.sqlteam.com/brettk/archive/2005/06/22/6328.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-11-03 : 15:16:34

and here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources