extract numbers from text - 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 2005 Forums

Transact-SQL (2005)

extract numbers from text

Author

Topic

maxchuie
Starting Member

2 Posts

Posted - 2007-12-06 : 14:51:56

how do extract the phone number only (123)558-9899 and display as a number value

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-06 : 15:09:43

My personal favorite is by calling a function I wrote (or borrowed - it's been so long). I responded to a post here with it - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93292&SearchTerms=StripPattern

Basically you'll call it and can specify a pattern like '%[^0-9]%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 15:13:06

Or use REPLACE function nested three times?

E 12°55'05.25"
N 56°04'39.16"

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-12-06 : 15:25:43

I still like the recursive use of PATINDEX / REPLACE myself. :) It allows some flexibility in case the formats come in with different characters or unexpected characters. In addition, it can be used for data cleansing operations too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources