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 |
|
Gerry.Perry@Avnet.com
Starting Member
6 Posts |
 Posted - 2004-02-27 : 16:42:23
|
HiI would like to make a copy of a string field but passed through a "compress function" such that characters such as space, '.', '-' are removed. e.g ABC-34,WER becomes ABC34WER.In essense I only want to "allow" A-Z and 0-9.Ideas anyone ?In the past and in other environments I've used VB/VBA but I dont know how to achieve in SQL SERVERThanksGerry  |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-27 : 16:45:22
|
| Use the REPLACE function:SELECT REPLACE(Column1, '.', '')FROM Table1To remove both:SELECT REPLACE(REPLACE(Column1, ',', ''), '-')FROM Table1You can also UPDATE your data in the same manner.Tara |
 |
|
|
Gerry.Perry@Avnet.com
Starting Member
6 Posts |
Posted - 2004-02-27 : 16:52:23
|
| Thanks. This certainly works but I need to exclude a whole host of 'invallid' characters so I guess to nest 20 or so woudl be onerous ?Gerry |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-27 : 16:54:58
|
| Yes you'll need to nest the REPLACEs.Tara |
|
|
|
Gerry.Perry@Avnet.com
Starting Member
6 Posts |
Posted - 2004-02-27 : 17:01:35
|
OK. Thanks and good nightGerry |
|
|
|
|
|
|
String compression