A Query..

Can anyone tell me a query to separate numeric characters from a string?

Ex

ColA

__

abcd 12

xyz 43 fas

iiil 34 gasd

Any query that gives me

Col A new column

__

abcd 12

xyz fas 43

iiil gasd 34

Thanks in advance

You could write a function and pass the column to that function. Basically, you need to parse out the characters you want. Here is one of many possible ways to do this. It's not the best performing query but it should be pretty easy to read and adjust for capturing the digits.

Good luck,
Bryan

Code Snippet

createfunction dbo.GetChars(@.string varchar(max))

returnsvarchar(max)

begin

declare @.ret varchar(max)

declare @.i int

set @.ret = @.string

set @.i = 1

while @.i > 0 begin

set @.i =patindex('%[0-9]%',@.ret)

if @.i > 0 begin

set @.ret =isnull(left(@.ret, @.i-1),'')+isnull(right(@.ret,len(@.ret)-@.i),'')

end

end

return @.ret

end

|||

Code Snippet

/***********************************************************************
** Script : To seperate character and Numeric in a given string (27-Jun-2007)
** Author : Vidhya Sagar
** E-mail : kvs1983@.gmail.com
** Version : 1.00
************************************************************************/
Create proc sp_sepcharnnum
@.word varchar(200) -- String to seperate
as begin
declare @.ascii varchar(20)
declare @.tot int
declare @.st int
declare @.word1 varchar(200)
declare @.num varchar(200)
set @.st=1
set @.word1=''
set @.num=''
select @.tot=len(@.word)
lop:
if @.st <= @.tot
begin
select @.ascii=ASCII(substring(@.word,@.st,1))
if @.ascii not between 48 and 57
begin
set @.word1 = @.word1 + char(@.ascii)
end
else
set @.num= @.num + char(@.ascii)
set @.st=@.st+1
goto lop
end
insert <DBNAME>..<TABLENAME> Either you can sepcify the dbname and table name or comment this line jus to show the output
select @.word1,@.num
end

Hi,
The above proc will help you to seperate char and numbers. It will expects a parameter to split it. You can either specify the dbname and table name (@. line 33 in proc)to store the seperated values in the table itself (note the table shud have two colums defined as char and int) or you can comment the line if you need the output directly in the result pane itself.

Reply me whether the above script is usefull or not.