Search and replace certain characters in a field

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)

Search and replace certain characters in a field

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-05 : 10:56:52

Felice writes "Need an efficient data clean-up process. Some fields are filled with control characters (such as carriage control), need to scan the entire table row by row, for each field (column), if there is a carriage control character, delete it and then update it back to the table. For example, if field "Driver Name" contains:

'Tracy' + carriage control + 'Austin'

Remove the carriage control, and save the field back as 'Tracy Austin'."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-05 : 11:08:13

Don't fall into the trap of confusing files with tables. Files are processed one record at a a time looking at each field. Tables are sets of data made up of rows that are made up of a set of columns. Take advantage of the set based nature of tables to perform your task....


update Felice
set [Driver Name] = replace(replace([Driver Name],char(10),''),char(13),'')

<O>

Edited by - Page47 on 07/05/2002 11:08:34

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources