isolate number in text/number 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 2005 Forums

Transact-SQL (2005)

isolate number in text/number string

Author

Topic

eevans
Starting Member

48 Posts

Posted - 2009-05-06 : 09:58:04

Hello,
I am working with fields that have both text and a number. I would like to run a query to return only the number.

The number does not have a set amount of digits. It may occur before, in the middle, or after the text. There are spaces in between the text and the number.

Examples:

Delta 15324
Alpha 82475 Bravo
7856 Echo

Any suggestions? Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-06 : 10:00:45

Try patindex

declare @t table (col1 varchar(20))
insert @t
select 'test' union all
select 'test 100' union all
select 'test 101 test'

select * from @t where patindex('%[0-9]%',col1) > 0