Like- SQL Server

Recently I got a query on how to use like clause. As we all know we can use to find word with pattern matching.
Here we will look a sample for it.


CREATE TABLE user_id_list (

user_id VARCHAR(256))


/*If you are using SQL Server 2008*/

INSERT INTO user_id_list

VALUES ('amish'),

('amish123'),

('amish.shah'),

('123456'),

('123amish')


/*If you are using SQL Server 2005*/

INSERT INTO user_id_list

SELECT 'amish'

UNION ALL

SELECT 'amish123'

UNION ALL

SELECT 'amish.shah'

UNION ALL

SELECT '123456'

UNION ALL

SELECT '123amish'


-- To get ID which has only alphabet in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z]%'


-- To get ID which has only alphabet and '.' in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z,.]%'


-- To get ID which has only alphabet and numbers in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z,0-9]%'