determine numeric values in string

[BobBob10]

Hi,

In a field called address I have records such as:

25 Steven Rd
Bloggs Avenue

How can I determine whether the first couple of charaters are numeric or text?

 

[vongrunt]

Define "first couple of characters" - one, two, until first space (that won't work for '25th')... ?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[BobBob10]

until the first space

 

[vongrunt]

Unfortutately MSSQL regexp support is kind of crude... so bullet-proof implementation is clunky:

declare @t table (addr varchar(32))
insert into @t values ('25 avenue somewhere')
insert into @t values ('42')
insert into @t values ('One Mircosoft Way, Redmond')
insert into @t values ('42foo')
insert into @t values ('null')

select addr, 
	case when addr like replicate('[0-9]', isnull(nullif(charindex(' ', addr)-1, -1), len(addr))) +'%' 
		then 'yep' 
		else 'nope' 
	end
from @t

If there is always at least one space you can remove ISNULL/NULLIF code and make things simpler.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Hehe, or maybe this one:

select addr,
	1 - abs(sign( patindex ('%[^0-9]%', addr) -	charindex (' ', addr)))
from @t

Result is 1 = true, 0 = false, NULL if input is NULL (without '' ). Basically this code searches for a) first non-numeric character, b) first space. If both positions are identical... bingo!

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[BobBob10]

cool, what does patindex ('%[^0-9]%', addr) do?

 

[BobBob10]

How can I extract the numeric values and place in another column?

 

[vongrunt]

> what does patindex ('%[^0-9]%', addr) do?

Returns position of first non-numeric character, 0 if no such character(s) exist.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vbkris]

try this too:
declare @temp_tbl table(col1 varchar(50))
insert into @temp_tbl values('25 aasdasd')
insert into @temp_tbl values('aa 22 sdasd')
insert into @temp_tbl values('12 aasdasd')
insert into @temp_tbl values('aasdasd')
insert into @temp_tbl values('212 1212 121211 aasdasd')

select CAST(substring(col1,1,charindex(' ',col1)) as int) TheNumber from @temp_tbl where col1 like '[0-9\s]%'

Known is handfull, Unknown is worldfull

 

[vongrunt]

Like I said, T-SQL regexp support is veryvery limited - \s or \b won't work

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vbkris]

thats strange, that worked for me...

Known is handfull, Unknown is worldfull