drops chracters - 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)

drops chracters

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-12-27 : 09:04:42

i am looking for scrpit that replacr all characters unwanted like :
',;[]-)( etc

for example:
fantazy'cou ->fantazy_cou
gertoyt.frt-> gertoyt_frt

i want to run it on all the tables in my DB.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-27 : 10:01:57

See if this helps
http://beyondrelational.com/blogs/madhivanan/archive/2009/05/11/removing-unwanted-characters.aspx

Madhivanan

Failing to plan is Planning to fail

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-27 : 10:20:24

It depends on how many entries and size of the tables but two options are

update tbl
set col = replace(replace(col,'.','_'),'-','_')
where col like '%[.-]%'

select 1
while @@rowcount > 0
update tbl
set col = stuff(col,patindex('%[.-]%',col),1,'_')
where col like '%[.-]%'

add replace statements and entries to the [.-] as needed - might have to escape some values.

You cn generate the statments to run on all character columns in the database.

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

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-12-28 : 01:35:43

how can i run it on all tables -all columns in my database ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 05:39:37

information_schema.columns will give you the columns in tables that are character datatype.
I would put that list into a table so you can keep track of the progress (also include the number of rows and start and end time).

Then you have to decide how you want to run it.
If your database is fairly small then I would go for the second process I suggested. You might want to start using that on small tables anyway.

You might create an sp to run it

create proc s_conv
@tbl varchar(128) ,
@col varchar(128)
as
declare @sql varchar(4000)
select @sql =
'select 1
while @@rowcount > 0
update [' + @tbl + ']
set [' + @col + '] = stuff(col,patindex(''%[.-]%'',[' + @col + ']),1,''_'')
where [' + @col + '] like '%[.-]%'
'
go
then call it via
declare @tbl varchar(128), @col varchar(128)

while exists (select * from convtbl where endtime is null
begin
select top 1 @tbl = tbl, @col = col
from convtbl
where endtime is null
order by rowcount

update convtbl
set starttime = getdate()
where @tbl = tbl and @col = col

exec s_conv @tbl, @col

update convtbl
set endtime = getdate()
where @tbl = tbl and @col = col
end

==========================================
Cursors are useful if you don't know sql.
SSIS 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