Using a regular expression to remove characters

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

Transact-SQL (2000)

Using a regular expression to remove characters

Author

Topic

jns
Starting Member

19 Posts

Posted - 2002-05-13 : 11:41:00

In SQL how would I remove any and all characters that are not 0 - 9?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-13 : 11:53:48

You can use the Replace function as part of an UPDATE statement:

UPDATE myTable SET myColumn=Replace(myColumn, 'A', '')

Each character you want removed should be replaced with an empty string. Unfortunately you'd either have to nest Replace statements or run multiple UPDATEs, or both.

You *might* be able to write a job that uses VBScript regular expressions to do the work, but it will be slow and you have to open an ADO recordset.

I imagine that you're cleaning up old data. If you need to ensure that new data does not include illegal characters, the best thing you can do is modify the input process to prevent non-numeric characters from being entered. Back that up with a constraint on the table that also does the same thing. Since it only needs to store digits, change the column into a numeric type, if possible.

jns
Starting Member

19 Posts

Posted - 2002-05-13 : 13:51:35

Actually my problem is this. I have a series of survey results with a separate table of the survey questions. The question numbers are, for example, 1, 2a, 2b, 2c, 3, 4, 5, 6, 7, 8, 9, 10, 11, 20, etc. When I try to sort the results by the question number I get:
1
10
2a
2b
2c
20
3
4
etc.

I need to sort by the question number by keep it in true order. I thought if I sorted by converting the question number to an int after removing any alpha characters would work but I can't figure out how to remove anything [A-z] in one call.

ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-13 : 14:10:52

This is gonna be an ugly looking expression, but it should work:

SELECT QuestionNumber FROM myTable
ORDER BY Cast(CASE IsNumeric(QuestionNumber)
WHEN 1 THEN QuestionNumber
ELSE SubString(QuestionNumber, 1, PatIndex('%[A-Z]%', QuestionNumber)-1) END
AS int)

You'll have to test this to be sure, but it's close.

jns
Starting Member

19 Posts

Posted - 2002-05-13 : 14:18:23

PERFECT!!!!

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources