TSQL String cleanup for just numeric characters

Question

Hi, I am working on cleaning up my address field and just bring back the first numeric characters from address field.

DECLARE @Address TABLE (Id INT IDENTITY, [Date] DATETIME,[Name] VARCHAR(10),[Address] VARCHAR(1000))
 
INSERT INTO @Address VALUES ('2/5/13 10:16 AM', 'Deep', '1094. NW Prage Apt #D1392 Minnesota MN 36812'),
								('2/5/13 8:18 AM', 'Rag', '13.52 .E Sandra Ln. 92@ Apt #989 Milwaukie WI 49529'),
								('2/5/13 9:26 AM', 'IR Tens', '.87 SE #9281K Glazen Dr. Chicago IL 63453')
SELECT * FROM @Address	

My resultset should be:

ID Number

1 1094

2 1352

3 87

Thanks in advance...................

Answer 1

Try -

SELECT Id,REPLACE(SUBSTRING(Address,1,(PATINDEX('%[a-z]%',Address))-1),'.','')
FROM @Address

Does the address contains dots ?

---

Narsimha

Answer 2

This sort of thing is often better done in the front end.  SQL doesn't have a a lot of string handling and regular expression capabilities.  But one way of doing it is SQL is (I added a couple of other cases to check handling where the whole string was numberic and where there were no numbers in the string).

DECLARE @Address TABLE (Id INT IDENTITY, [Date] DATETIME,[Name] VARCHAR(10),[Address] VARCHAR(1000))
 
INSERT INTO @Address VALUES ('2/5/13 10:16 AM', 'Deep', '1094. NW Prage Apt #D1392 Minnesota MN 36812'),
                                ('2/5/13 8:18 AM', 'Rag', '13.52 .E Sandra Ln. 92@ Apt #989 Milwaukie WI 49529'),
                                ('2/5/13 9:26 AM', 'IR Tens', '.87 SE #9281K Glazen Dr. Chicago IL 63453'),
                                ('2/3/13 3:18 AM', 'TestAll', '123'),
                                ('2/2/13 2:18 AM', 'TestNone', 'abc'),
                                ('2/2/13 5:55 AM', 'TestNone', 'xyz567');

;With cte1stNonNum As
(Select ID, Address, PatIndex('%[0-9]%', Address) As FirstNumeric
From @Address),
cteFirstNumeric As
(Select ID, Address, FirstNumeric, Case When FirstNumeric > 0 Then PatIndex('%[^0-9]%', SubString(Address, FirstNumeric, Len(Address))) Else Null End As FirstNonNumeric 
From cte1stNonNum)
Select ID, Case When FirstNonNumeric Is Null Then Null 
           When FirstNonNumeric = 0 And FirstNumeric = 1 Then Address 
           When FirstNonNumeric = 0 And FirstNumeric <> 1 Then SubString(Address, FirstNumeric, Len(Address)) 
           Else SubString(Address, FirstNumeric, FirstNonNumeric-1) End As Number
From cteFirstNumeric;

Tom

Answer 3

This is a typical use case for a CLR stored procedure or function where writing this is a breeze, where as you will have to break your neck to do it in T-SQL.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Thanks all for your response and inputs...........