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.
| Author |
Topic |
|
bangingtunes
Starting Member
23 Posts |
 Posted - 2003-02-06 : 09:09:24
|
| Hi. Im importing data from one DB to another. Along the way i need to check fields for invalid characters.Is there an easy way of me checking for a string of invalid characters?I can only think of using the CHARINDEX for each character using begin end. Im not even sure how i could loop through the invalid characters.Kristian |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-06 : 09:17:35
|
| You can TEST for invalid characters using LIKE:SELECT * FROM myTable WHERE myCol LIKE '%[!@#$%~]%'You would list all of the invalid characters in between the square brackets. Unfortunately you wouldn't be able to replace all of them in one shot. You'd have to use nested Replace() function calls to replace each invalid character. |
 |
|
|
bangingtunes
Starting Member
23 Posts |
Posted - 2003-02-06 : 09:43:29
|
| That looks a lot easier, thanks.If i wanted to simply replace them all with nothing i.e remove the offending characters, is that possible in one shot using square brackets.Ill have a play around and see how i get on.ThanksKristian |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-06 : 09:46:27
|
| No. Something like this:UPDATE myTableSET myCol=Replace(Replace(Replace(myCol, '!', ''), '$', ''), '*', '')Each character you want to replace would need its own function call. Actually it's not hard to do, just tedious, and if you do it with nested calls you can get them all replaced with one UPDATE statement. |
|
|
|
|
|
|
Is there a better method