November 14, 2007 at 3:31 am
Hello everyone,
I got the task to clean some address data. My biggest problem is a column which should contain streetnumbers but quite often also contain charcters.
Here's an example
use tempdb
CREATE TABLE test
(c1 varchar(15))
go
INSERT INTO test VALUES('36 A')
INSERT INTO test VALUES('39-43')
INSERT INTO test VALUES('37 - 8e etage')
INSERT INTO test VALUES('40-F')
INSERT INTO test VALUES('43 bg')
INSERT INTO test VALUES('49 hs')
INSERT INTO test VALUES('C56')
INSERT INTO test VALUES('18 3hg')
INSERT INTO test VALUES('5-23')
INSERT INTO test VALUES('723 C7.062')
SELECT ??? FROM test
--Desired result
36
39
37
40
43
49
18
5
723
So I'm looking only for the first number and if the value starts with a character, then return an empty value. I tried using patindex to determine the first non-numeric character but can get it working.
Any help is welcome
Markus
[font="Verdana"]Markus Bohse[/font]
November 14, 2007 at 3:43 am
There is a UDF on http://blog.sqlauthority.com/2007/04/11/sql-server-udf-user-defined-function-to-extract-only-numbers-from-string/
that does this.
Regards,
Andras
November 14, 2007 at 4:02 am
Andras,
thanks for the response, but this function returns all the numbers from the string. Also it goes through the string one character at the time and I was hoping for something more efficient by searching for the patindex of the first non-numeric character.
But I can probably adjust the function so that it fit's my needs.
Markus
[font="Verdana"]Markus Bohse[/font]
November 14, 2007 at 7:35 am
select left(c1,patindex('%[^0-9]%',c1)-1)
from test
November 14, 2007 at 7:38 am
Thanks Hans,
that's exactly what I was looking for.
Markus
[font="Verdana"]Markus Bohse[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply