Wednesday, November 02, 2005

Finding ASCII, Numbers and Alphabet Characters In SQL Server Tables

SQL Server has limited support for regular expressions,
You can run the code below to see what some of the results are by searching for data while using regular expressions

CREATE TABLE AsciiTest (id INT identity, TextField VARCHAR(50))
INSERT INTO AsciiTest
SELECT '%&&&123%#$#$'
UNION ALL
SELECT 'safdsfsdrfsdfse'
UNION ALL
SELECT '12121212'
UNION ALL
SELECT '1212asasas'
UNION ALL
SELECT '%&&&%#$#$'
UNION ALL
SELECT '4564gg565656'
UNION ALL
SELECT '12'

--No Alphabet Characters
SELECT * FROM AsciiTest
WHERE TextField NOT LIKE '%[a-Z]%'

--Starting with a number
SELECT * FROM AsciiTest
WHERE TextField LIKE '[0-9]%'

--Ending with a number
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[0-9]'

--Numbers and characters
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[0-Z]%'

--Only non alphabet/numbers characters
SELECT * FROM AsciiTest
WHERE TextField NOT LIKE '%[0-Z]%'


--some non alphabet/number characters
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[^0-Z]%'

-- ASCII Code 38 (&)
SELECT * FROM AsciiTest
WHERE TextField LIKE '%' + char(38) + '%'

No comments: