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 |
|
vpathisseril
Starting Member
6 Posts |
 Posted - 2003-10-17 : 09:36:48
|
| Varghese writes ""I want to eliminate hyphens, apostrophes,blanks,persods,suffixes(Jr.) or Perefixes (Dr.)from the below statement.Wht would be the best way to approcah this.ThanksVargheseSelect LName, FName from EMPTABLELName FNameEstep, Jr. NoahLeyva - Capet JavierBelliveau Jr VernonThomas, TylerO'callaghan TerrenceUriarte JoseBarrios Hector, JrEsquivias AntonioGuerrero MezaAntonioLlanes JesusSalcedo Manuel" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-10-17 : 09:45:38
|
| select replace(replace(lname,',',''),'-','') from emptableexpand the replace as necessary....(for all the dud characters you need stripped out)the prefix and suffix will need to be treated first...(as replacing the period, will negate the search for jr./dr.)the prefix/suffix may need to be targeted at the front + end of the search string....because that's where they're meant to be? |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-17 : 11:48:58
|
| You might find it easier but slower to put all the strings you want to replace into a table (or a table variable in the SP) and loop through it doing the replaces. That will make it easier to add entries.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Eliminate Char