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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-26 : 17:40:12
|
David writes "Data Matching - Parsing a string for an array of strings and replacing:
I have a table that contains a field (GNMT) which I need to parse and replace all instances of a given set of symbols (or words) with another set of words or an empty string, in order to match that data from one system to another.
To make this easier to explain, the field GNMT contains:
JOE BLOGGS & DAPHNE BLOGGS TRADING AS THE BLOGGSES
1. I need to parse this string and replace the special characters with an empty string so that it becomes:
JOB BLOGGS DAPHNE BLOGGS TRADING AS THE BLOGGSES
2. I then need to parse the string for all instances of "Trading As" (i.e. "T /A", "T/AS", "TDG AS") and replace these with "T/A" to make the string match the mainframe system.
I must stress at this point that this can be achieved using a long-winded Replace function, such as the following two snippets:
(using Query Analyzer for example)
1.
SELECT Replace(Replace(Replace(Replace(Replace(Replace(Replace(GNMT,'^',''),'%',''),'!',''),'#',''), '@',''), '$',''), '\',''),FROM CLEANUPTABLE
WHERE GNMT LIKE '%[\$@#!%^*+?]%'
2.
Set @TA = 'T/A'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GNMT, 'TRADING AS',@TA), 'T /A',@TA), 'T\A',@TA),'T/AS',@TA),'TDG AS',@TA)
WHERE GNMT LIKE '%TRADING AS%'
OR GNMT LIKE '%T /A%'
OR GNMT LIKE '%T\A%'
OR GNMT LIKE '%T/AS%'
OR GNMT LIKE '%TDG AS%'
**** BUT SURELY THERE IS AN EASIER WAY?!?! (perhaps a function, but I can see that these aren't readily available until SQL 2000)
**** Please tell me that there is an easier way, as this type of function would be VERY handy!!
Version information: SQL Server 7 (SP2) on Windows 2000" |
|
|
|
|
|
|
|
Data Matching - Parsing a string for an array of strings and replacing