过滤无效字符串的方法 ----------T-SQL

1.第一个方法

      

1. CREATE FUNCTION [dbo].[RemoveNonAlphaCharacters](@Temp VARCHAR(1000))
2. RETURNS VARCHAR(1000)
3. AS
4. BEGIN
5.  
6.     WHILE PATINDEX('%[^a-z]%', @Temp) > 0
7.         SET @Temp = STUFF(@Temp, PATINDEX('%[^a-z]%', @Temp), 1, '')
8.  
9.     RETURN @TEmp
10. END

2.第二个方法

  

1. CREATE FUNCTION [dbo].[RemoveNonAlphaNumericCharacters](@Temp VARCHAR(1000))
2. RETURNS VARCHAR(1000)
3. AS
4. BEGIN
5.  
6.     WHILE PATINDEX('%[^a-z^0-9]%', @Temp) > 0
7.         SET @Temp = STUFF(@Temp, PATINDEX('%[^a-z^0-9]%', @Temp), 1, '')
8.  
9.     RETURN @TEmp
10. END

3.第三个方法

  

 

  

1. CREATE FUNCTION [dbo].[RemoveNonNumericCharacters](@Temp VARCHAR(1000))
2. RETURNS VARCHAR(1000)
3. AS
4. BEGIN
5.  
6.     WHILE PATINDEX('%[^0-9]%', @Temp) > 0
7.         SET @Temp = STUFF(@Temp, PATINDEX('%[^0-9]%', @Temp), 1, '')
8.  
9.     RETURN @TEmp
10. END

As you can see, the real work is done by using the like comparison functionality of the PatIndex function.

'%[^a-z]%'
'%[^a-z0-9]%'
'%[^0-9]%'

It should be relatively simple to modify the previous functions to suit any sort of comparisons. You can use it to remove ranges of characters, or even for single characters (similar to the replace function).

For example, if you wanted to remove certain punctuation characters, but leave everything else, you could use '%[^.^,^-]%' (to remove dot, comma, and dash).

You can use the following code to test this functionality. It creates a table variable with some sample data. The query at the end returns the original data, and another column showing the output from the three functions presented here.

 

 

4.调用这些方法

1. DECLARE @Test TABLE(DATA VARCHAR(100))
2.  
3. INSERT INTO @Test VALUES('(111) 222-3333')
4. INSERT INTO @Test VALUES('111-222-3333')
5. INSERT INTO @Test VALUES('111.222.3333')
6. INSERT INTO @Test VALUES('(800) XXX-3333')
7. INSERT INTO @Test VALUES('')
8. INSERT INTO @Test VALUES(NULL)
9.  
10. SELECT  DATA,
11.         dbo.RemoveNonAlphaCharacters(DATA) AS AlphaOnly,
12.         dbo.RemoveNonAlphaNumericCharacters(DATA) AS AlphaNumericOnly,
13.         dbo.RemoveNonNumericCharacters(DATA) AS NumericOnly
14. FROM    @Test

5.产生的结果

    Data           AlphaOnly   AlphaNumericOnly NumericOnly
---------      ----------- ---------------- -----------
(111) 222-3333             1112223333       1112223333
111-222-3333               1112223333       1112223333
111.222.3333               1112223333       1112223333
(800) XXX-3333 XXX         800XXX3333       8003333

NULL           NULL        NULL             NULL