Replace function - 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

SQL Server Development (2000)

Replace function

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-03-30 : 09:08:13

guys

I have a column which has values like 9800P3337, 980CR400A etc
I want to build a script which replaces only the charecters between 2 and 8 positions of column values by 0,
So for 9800P3337 - 980003337, 98000400A

I have tried to use combination replace and substring functions but it doesnt seem to work.

Any suggestions/inputs would be very helpful indeed.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-30 : 09:47:03

declare @s varchar(100)
select @s = '980CR400A'

select substring(@s,1,1)
+ case when substring(@s,2,1) like '[^0-9]' then '0' else substring(@s,2,1) end
+ case when substring(@s,3,1) like '[^0-9]' then '0' else substring(@s,3,1) end
+ case when substring(@s,4,1) like '[^0-9]' then '0' else substring(@s,4,1) end
+ case when substring(@s,5,1) like '[^0-9]' then '0' else substring(@s,5,1) end
+ case when substring(@s,6,1) like '[^0-9]' then '0' else substring(@s,6,1) end
+ case when substring(@s,7,1) like '[^0-9]' then '0' else substring(@s,7,1) end
+ case when substring(@s,8,1) like '[^0-9]' then '0' else substring(@s,8,1) end
+ substring(@s,9,1)

==========================================
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.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-03-30 : 10:16:05

thanks nr for the solution.
But when I run the above query on the column which I intend to change(it has 54000 values to be changed), it only
brings uo the last row.
Am I missing something with respect to looping through entire column values, please suggest.

And how do I change it to update instead of just doing the select which is the whole purpose of my problem

Thanks for your inputs

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-30 : 10:50:16

update tbl
set fld = substring(fld,1,1)
+ case when substring(fld,2,1) like '[^0-9]' then '0' else substring(fld,2,1) end
+ case when substring(fld,3,1) like '[^0-9]' then '0' else substring(fld,3,1) end
+ case when substring(fld,4,1) like '[^0-9]' then '0' else substring(fld,4,1) end
+ case when substring(fld,5,1) like '[^0-9]' then '0' else substring(fld,5,1) end
+ case when substring(fld,6,1) like '[^0-9]' then '0' else substring(fld,6,1) end
+ case when substring(fld,7,1) like '[^0-9]' then '0' else substring(fld,7,1) end
+ case when substring(fld,8,1) like '[^0-9]' then '0' else substring(fld,8,1) end
+ substring(fld,9,1)

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources