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
 Development Tools
 Other Development Tools
 String compression

Author  Topic 

Gerry.Perry@Avnet.com
Starting Member

6 Posts

Posted - 2004-02-27 : 16:42:23
Hi
I 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 SERVER

Thanks

Gerry

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 16:45:22
Use the REPLACE function:

SELECT REPLACE(Column1, '.', '')
FROM Table1

To remove both:

SELECT REPLACE(REPLACE(Column1, ',', ''), '-')
FROM Table1

You can also UPDATE your data in the same manner.

Tara
Go to Top of Page

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
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 16:54:58
Yes you'll need to nest the REPLACEs.

Tara
Go to Top of Page

Gerry.Perry@Avnet.com
Starting Member

6 Posts

Posted - 2004-02-27 : 17:01:35
OK. Thanks and good night

Gerry
Go to Top of Page
   

- Advertisement -