Help Replacing Numerics - 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 2005 Forums

Transact-SQL (2005)

Help Replacing Numerics

Author

Topic

sriv
Starting Member

11 Posts

Posted - 2009-04-08 : 11:27:46

Hi All,
I have a string which looks like this.

AsVVscDB,BBcfGHmLPD,AA75Cs,SSa,GGBB2acklABC,TUkh

Every segment which has a numeric needs to be removed. The final string would look like AsVVscDB,BBcfGHmLPD,SSa,TUkh. Any ideas. Ideally, if this could work both on SQL Server 2005 and 2000, would be great. Thank you very much.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 12:42:45

Is this string in a certain field of a table?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sriv
Starting Member

11 Posts

Posted - 2009-04-08 : 13:16:01

Yes Sir, in a varchar(200) field. Thank you.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-08 : 14:04:48

declare @s varchar(40)
set @s='jsdhdfgAd78456fjsdf937845834'
declare @result varchar(40)
set @result=''select @result=@result + case when number like '%[a-z]%'then number else''end from
(
select substring(@s,number,1) as number from
(
select number from master.dbo.spt_values where type='p' and number between 1 and len(@s))as t
)as t

select @result

I had scribbled the above query on a page long time back but quite dont remember from where I got it.All the credit goes to the original author.

sriv
Starting Member

11 Posts

Posted - 2009-04-08 : 14:11:51

Hi Ayamas,
Thanks for the solution. I believe it is from Tony Rogerson. However, this solution does not help me. As posted, I need to remove each block which has a numeric in it.
AsVVscDB,BBcfGHmLPD,AA75Cs,SSa,GGBB2acklABC,TUkh will become AsVVscDB,BBcfGHmLPD,SSa,TUkh because AA75Cs and GGBB2acklABC will be removed.
Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-08 : 14:15:10

since you somehow have a comma separated list in ONE column *shudder*, you will need to split the column. Do a search for "Split function".

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-08 : 14:15:21

Oh you mean it the value will be comma seperated?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 14:20:48

this should get you started, I believe. This solution comes from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121563&SearchTerms=echois

change table name and fields to your table

;With Your_CTE (ID,ParamData)
AS
(SELECT t.Ident,f.Val
FROM juju t
CROSS APPLY dbo.ParseValues(t.juju,',')f
)
SELECT *
FROM Your_CTE
WHERE ParamData NOT LIKE '%[0-9]%'

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-08 : 14:22:09

Yes there are many split functions you can refer to but this is the one I would use.

declare @s varchar(200)
set @s='AsVVscDB,BBcfGHmLPD,AA75Cs,SSa,GGBB2acklABC,TUkh'
declare @x xml
set @x='<i>'+ Replace(@s,',','</i><i>') + '</i>'

select * from
(
select x.i.value('.','varchar(200)')as val from @x.nodes('//i')x(i)
)t where val not like '%[0-9]%'

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 14:32:14

Yes ayamas' looks better no need for function there but how will it work for a table field and then put it back together in the right order :)
from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
;With Your_CTE (ID,ParamData)
AS
(SELECT t.Ident,f.Val
FROM juju t
CROSS APPLY dbo.ParseValues(t.juju,',')f
)
--SELECT *
-- FROM Your_CTE
--WHERE ParamData NOT LIKE '%[0-9]%'

SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.ParamData FROM Your_CTE AS s2 WHERE s2.ID = s1.ID AND ParamData NOT LIKE '%[0-9]%' FOR XML PATH('')), 1, 1, '') AS CODES
FROM Your_CTE AS s1
WHERE ParamData NOT LIKE '%[0-9]%'
ORDER BY s1.ID

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sriv
Starting Member

11 Posts

Posted - 2009-04-08 : 14:45:30

Thank you guys, this is beautiful. Much appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-09 : 02:20:53

quote:
Originally posted by ayamas

declare @s varchar(40)
set @s='jsdhdfgAd78456fjsdf937845834'
declare @result varchar(40)
set @result=''select @result=@result + case when number like '%[a-z]%'then number else''end from
(
select substring(@s,number,1) as number from
(
select number from master.dbo.spt_values where type='p' and number between 1 and len(@s))as t
)as t

select @result

I had scribbled the above query on a page long time back but quite dont remember from where I got it.All the credit goes to the original author.

Probably from this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-09 : 02:55:35

quote:
Originally posted by madhivanan

quote:
Originally posted by ayamas

declare @s varchar(40)
set @s='jsdhdfgAd78456fjsdf937845834'
declare @result varchar(40)
set @result=''select @result=@result + case when number like '%[a-z]%'then number else''end from
(
select substring(@s,number,1) as number from
(
select number from master.dbo.spt_values where type='p' and number between 1 and len(@s))as t
)as t

select @result

I had scribbled the above query on a page long time back but quite dont remember from where I got it.All the credit goes to the original author.

Probably from this
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

Yes now I remember.

dsindo
Starting Member

45 Posts

Posted - 2009-04-10 : 18:29:00

declare @str varchar(1000)
,@str2 varchar(1000)

set @str='AsVVscDB,BBcfGHmLPD,AA75Cs,SSa'
set @str2='GGBB2acklABC,TUkh'

if object_id('tempdb..#temp') is not null
drop table #temp
create table #temp (text1 varchar(100))

insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str,',','.'),4)) > 1 then '' else parsename(replace(@str,',','.'),4) end
insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str,',','.'),3)) > 1 then '' else parsename(replace(@str,',','.'),3) end
insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str,',','.'),2)) > 1 then '' else parsename(replace(@str,',','.'),2) end
insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str,',','.'),1)) > 1 then '' else parsename(replace(@str,',','.'),1) end
insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str2,',','.'),2)) > 1 then '' else parsename(replace(@str2,',','.'),2) end
insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str2,',','.'),1)) > 1 then '' else parsename(replace(@str2,',','.'),1) end

declare @str3 varchar(1000)
select @str3=coalesce(@str3+',','' ) + text1 from #temp
select @str3

dsindo
Starting Member

45 Posts

Posted - 2009-04-10 : 18:30:40

better yet,

declare @str varchar(1000)
,@str2 varchar(1000)

set @str='AsVVscDB,BBcfGHmLPD,AA75Cs,SSa'
set @str2='GGBB2acklABC,TUkh'

if object_id('tempdb..#temp') is not null
drop table #temp
create table #temp (text1 varchar(100))

insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str,',','.'),4)) > 1 then '' else parsename(replace(@str,',','.'),4) end
insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str,',','.'),3)) > 1 then '' else parsename(replace(@str,',','.'),3) end
insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str,',','.'),2)) > 1 then '' else parsename(replace(@str,',','.'),2) end
insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str,',','.'),1)) > 1 then '' else parsename(replace(@str,',','.'),1) end
insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str2,',','.'),2)) > 1 then '' else parsename(replace(@str2,',','.'),2) end
insert into #temp select case when patindex('%[0-9]%', parsename(replace(@str2,',','.'),1)) > 1 then '' else parsename(replace(@str2,',','.'),1) end

declare @str3 varchar(1000)
select @str3=coalesce(@str3+',','' ) + text1 from #temp
select replace(@str3,',,',',')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources