Character Replacement - 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)

Character Replacement

Author

Topic

mohdrashid
Starting Member

23 Posts

Posted - 2004-12-27 : 02:17:24

hi all,
i am doing some data cleansing work. i got a column with the following value:

scr0989y
sbh1309m
ev0099y
scr4000y

i need to convert the above to

scr989y
sbh1309m
ev99y
scr4000y

using replace function does not work as scr4000y becomes scr4y which is wrong.
any ideas?
thanks in advance.
rashid

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-27 : 05:05:43

[code]select
col,
case patindex('%[^0-9]0%',col)
when 0 then col
else replace(col,'0','') end as replaced
from
(select 'scr0989y' as col union all
select 'sbh1309m' union all
select 'ev0099y' union all
select 'scr4000y' )t[/code]

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-27 : 09:05:13

ooops,

select
	col,
	substring(col,1,patindex('%[^0-9][0-9]%',col)) +
	ltrim(cast(substring(col,patindex('%[^0-9][0-9]%',col)+1,patindex('%[0-9][^0-9]%',col)-patindex('%[^0-9][0-9]%',col)) as int)) +
	substring(col,patindex('%[0-9][^0-9]%',col)+1,len(col)) as replaced
	/*,case patindex('%[^0-9]0%',col)
		when 0 then col
		else replace(col,'0','') end as replaced_bad*/
from
	(select 'scr0989y' as col union all
	select 'sbh1309m' union all
	select 'ev0099y' union all
	select 'ev009909900y' union all
	select 'scr4000y' )t

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-27 : 11:07:03

A bit more long winded maybe but neater.

select left(col, fst-1) + convert(varchar(10),convert(int,substring(col, fst, lst-fst+1))) + right(col,len(col)-lst)
from (select
col,
fst = patindex('%[0-9]%',col) ,
lst = len(col) - patindex('%[0-9]%',reverse(col)) + 1
from
(select 'scr0989y' as col union all
select 'sbh1309m' union all
select 'ev0099y' union all
select 'ev009909900y' union all
select 'scr4000y' )t
) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-27 : 11:47:46

>>"A bit more long winded maybe but neater."
Waddya mean neater!

rockmoose

mohdrashid
Starting Member

23 Posts

Posted - 2004-12-28 : 06:56:15

wow! did not know some the functions/command existed in the first place.
will study the solutions.
thanks anyway to all
rashid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources