Strip numerics from varchar - 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 2000 Forums

Transact-SQL (2000)

Strip numerics from varchar

Author

Topic

makimark
Starting Member

34 Posts

Posted - 2004-04-18 : 16:29:33

Hi

I need to strip out numerics from a string in a column. The string looks like this: '123 sdfsdfsdf' or '12 dfsdfsdfsd' or '2324 dfdfd'
There is always a space after the number. I have tried using like like '[0-9]%' or isnumeric but this is not working 100%.

anyone have some pointers ?

thanks

gpl
Posting Yak Master

195 Posts

Posted - 2004-04-18 : 16:53:53

Try this

-- assume variable @String contains your string

DECLARE @SeparatorPos int

-- find where the space is
SET @SeparatorPos = PATINDEX('% %', @String)
-- replace everything up to and including the space with nothing
SET @String = STUFF(@String, 1, @SeparatorPos, '')

there you are, Robert's your father's brother

Graham

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-04-18 : 17:09:39

[code]create table test ( v varchar(20) )

insert into test
select '123 sdfsdfsdf' union all
select '12 dfsdfsdfsd' union all
select '2324 dfdfd'

select substring(v,charindex(' ',v,1)+1,len(v))
from test

drop table test[/code]

makimark
Starting Member

34 Posts

Posted - 2004-04-18 : 17:09:55

Thanks, will give it a try

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources