Is there a way you can find and replace words in sql? I need to replace certain chars within a couple of columns, !"£$%^&*()_+ etc and spaces with an underscore _

so for example, es.c0408.010 with es_c0408_010

Any help, as always is greatly received.

Stuart

See the Replace funtion in BOL:
this will replace a '.' with an '_':
REPLACE ( <Column> , '.', '_')
You can use it within and Update statement or select statement.

This is great, Thanks

update tbl_product_import
set [new sku] = REPLACE ( [new sku] , '.', '_')
where [new sku] like '%.%'

Is there a way though that I could amend this to replace !"£$% aswell in the same pass? kinda like an or/and?

You do not need the where clause in your select statement. It will only replace if the char(s) exist in that column. And, I belive you will need to run a separate UPDATE for each character you want to replace.

Could I reference another table of illegal characters? How would I integrate this?

problem with referencing a table of "illegal" characters is how would you populate them. I suggest storing the ascii value of illegal characters and use sql to convert/compare them if thats the direction you want to go.

I set each character in turn into a dts package like the one above, only there are 35,000 product codes which could potiential have an 'illegal' character, maybe even two! It would seem each dts sql script time outs out and doesn't do anything. :-(

update tbl_product_import
set [new sku] = REPLACE ( [sku] , '.', '_')

Any ideas on a better method?

Stuart

You can "fix" the data with either DTS or sql code. I prefer SQL code but thats me. The real solution is to prevent the bad data from being loaded in the first place. This should best be done on the source system. I am more of a backend developer so I prefer the back end tools over things like DTS.

But the data is given to me three times daily from 10 different sources.