Wednesday, November 2, 2011

SQL Regex

http://beyondrelational.com/blogs/naomi/archive/2011/10/19/remove-bad-characters-from-a-string.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2011Nov02

01.CREATE FUNCTION dbo.RemoveBadChars
02. (@String VARCHAR(8000),
03. @AllowedPattern VARCHAR(100))
04.RETURNS VARCHAR(8000)
05.AS
06. BEGIN
07. DECLARE @nBadChars INT,
08. @Rest VARCHAR(100)
09.
10. SET @String = '!@#$% 123 ??abc##rd,,,'
11.
12. SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String)
13.
14. WHILE @nBadChars > 0
15. BEGIN
16. SET @Rest = Substring(@String,@nBadChars + 1,Len(@String))
17.
18. SET @String = Substring(@String,1,@nBadChars - 1)
19.
20. SET @Rest = Substring(@Rest,Patindex('%[' + @AllowedPattern + ']%',@Rest),
21. Len(@Rest))
22.
23. SET @String = @String + @Rest
24.
25. SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String)
26. END
27.
28. RETURN @String
29. END
30.
31.GO



1.SELECT dbo.RemoveBadChars('1234@@@@@@@@@????????djwkejwk^&*-+=a',' 0-9a-z')

No comments: