Jack I am sorry it worked with your example, but when I worked with my test data its not working.. I should return only 1st 5 records but its returning more..could you please help me resolve this issue

here is my test data

IF OBJECT_ID('dbo.#frUser_Copy') IS NOT NULL BEGIN DROP TABLE #frUser_Copy END CREATE TABLE #frUser_Copy ( UserName VARCHAR(25) )GoINSERT INTO #frUser_Copy([UserName])VALUES ('shashi>')INSERT INTO #frUser_Copy([UserName])VALUES ('sh>shashi')INSERT INTO #frUser_Copy([UserName])VALUES ('shashi"&')INSERT INTO #frUser_Copy([UserName])VALUES ('shash?34')INSERT INTO #frUser_Copy([UserName])VALUES ('sh&&?34')INSERT INTO #frUser_Copy([UserName])VALUES ('22jsnell')INSERT INTO #frUser_Copy([UserName])VALUES ('71jsnell')INSERT INTO #frUser_Copy([UserName])VALUES ('71rowens')INSERT INTO #frUser_Copy([UserName])VALUES ('99jsnell')INSERT INTO #frUser_Copy([UserName])VALUES ('abcrep')INSERT INTO #frUser_Copy([UserName])VALUES ('abcuser')INSERT INTO #frUser_Copy([UserName])VALUES ('acctrep01')INSERT INTO #frUser_Copy([UserName])VALUES ('acctrep1')INSERT INTO #frUser_Copy([UserName])VALUES ('Admin')INSERT INTO #frUser_Copy([UserName])VALUES ('Andrew')INSERT INTO #frUser_Copy([UserName])VALUES ('ANewUser')INSERT INTO #frUser_Copy([UserName])VALUES ('asingh')INSERT INTO #frUser_Copy([UserName])VALUES ('askillmeyer')INSERT INTO #frUser_Copy([UserName])VALUES ('B2BFiler10')INSERT INTO #frUser_Copy([UserName])VALUES ('BEHenri')INSERT INTO #frUser_Copy([UserName])VALUES ('BEOnlineAcct01')INSERT INTO #frUser_Copy([UserName])VALUES ('bermudafiler')INSERT INTO #frUser_Copy([UserName])VALUES ('BEtesting')INSERT INTO #frUser_Copy([UserName])VALUES ('bhanup')INSERT INTO #frUser_Copy([UserName])VALUES ('bhartmere')INSERT INTO #frUser_Copy([UserName])VALUES ('bhetju')INSERT INTO #frUser_Copy([UserName])VALUES ('bhetjun')INSERT INTO #frUser_Copy([UserName])VALUES ('bhetjunk')INSERT INTO #frUser_Copy([UserName])VALUES ('bhetland')INSERT INTO #frUser_Copy([UserName])VALUES ('BillBradley')INSERT INTO #frUser_Copy([UserName])VALUES ('bmorgan')INSERT INTO #frUser_Copy([UserName])VALUES ('bpersha')INSERT INTO #frUser_Copy([UserName])VALUES ('ccharlier')INSERT INTO #frUser_Copy([UserName])VALUES ('ccharlier01')INSERT INTO #frUser_Copy([UserName])VALUES ('ccharlierext')INSERT INTO #frUser_Copy([UserName])VALUES ('cici11')INSERT INTO #frUser_Copy([UserName])VALUES ('ClaudiaOrg')INSERT INTO #frUser_Copy([UserName])VALUES ('cmylavarapu')INSERT INTO #frUser_Copy([UserName])VALUES ('crcharlier')INSERT INTO #frUser_Copy([UserName])VALUES ('CTCorp')INSERT INTO #frUser_Copy([UserName])VALUES ('CTCorpJR')INSERT INTO #frUser_Copy([UserName])VALUES ('cvanroy')INSERT INTO #frUser_Copy([UserName])VALUES ('cvanroy1')INSERT INTO #frUser_Copy([UserName])VALUES ('ddiacont')INSERT INTO #frUser_Copy([UserName])VALUES ('dmacd')INSERT INTO #frUser_Copy([UserName])VALUES ('dmacdougall')INSERT INTO #frUser_Copy([UserName])VALUES ('dmason')INSERT INTO #frUser_Copy([UserName])VALUES ('dmasononline2')INSERT INTO #frUser_Copy([UserName])VALUES ('dmasononline3')INSERT INTO #frUser_Copy([UserName])VALUES ('dsingh')INSERT INTO #frUser_Copy([UserName])VALUES ('ebarnard')INSERT INTO #frUser_Copy([UserName])VALUES ('Elisa42')INSERT INTO #frUser_Copy([UserName])VALUES ('eottesen')INSERT INTO #frUser_Copy([UserName])VALUES ('ExternalFiler01')INSERT INTO #frUser_Copy([UserName])VALUES ('externalrole')INSERT INTO #frUser_Copy([UserName])VALUES ('FileOneAdmin')INSERT INTO #frUser_Copy([UserName])VALUES ('FM14Test')INSERT INTO #frUser_Copy([UserName])VALUES ('general')INSERT INTO #frUser_Copy([UserName])VALUES ('gilligan')INSERT INTO #frUser_Copy([UserName])VALUES ('guestuser')INSERT INTO #frUser_Copy([UserName])VALUES ('hbali')INSERT INTO #frUser_Copy([UserName])VALUES ('henrithomas')INSERT INTO #frUser_Copy([UserName])VALUES ('HermeetBali123')INSERT INTO #frUser_Copy([UserName])VALUES ('hthomas')INSERT INTO #frUser_Copy([UserName])VALUES ('hthomas1')INSERT INTO #frUser_Copy([UserName])VALUES ('internaluser')INSERT INTO #frUser_Copy([UserName])VALUES ('jhenderson01')INSERT INTO #frUser_Copy([UserName])VALUES ('jjones')INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesalt1')INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesalternate')INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesext2')INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesexternal')INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesexternal1')INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesformer')

SELECT *FROM #frUser_Copy WHERE [UserName] LIKE '%[^a-z]%' AND [UserName] LIKE '%[^0-9]%' AND [UserName] LIKE '%[^A-Z]%'

The AND NOT's can get pretty confusing, so just to explain this a bit more:

The problem is the logic of your where clause. Here's what you're saying(assuming case sensitivity doesn't matter for this explanation):

WHERE

[UserName] LIKE '%[^a-z]%' AND

This will pull in anything with a character other than a-z. So any string with a number passes this test.

[UserName] LIKE '%[^0-9]%' AND

This will pull in anything with a character other than 0-9, so anything with a letter passes this test.

[UserName] LIKE '%[^A-Z]%'

This will again, pull in anything with a character other than A-Z, so all strings with numbers pass this again.

Your end result? All strings with a non alphanumeric character(which is what you're after), as well as all strings with both a number and a letter, because these are evaluated separately.

Combining them like Jack shows above(or with a case sensitive collation using LIKE '%[^a-zA-Z0-9]%' looks for a character outside of *all* of those ranges at once, not each individually, which is what you're trying to do.

Also, if you were evaluating only a single character, your WHERE clause would be OK, because of the AND's. A single character would never pass all those tests if it was a number or letter, (letters fail the first or third, numbers fail the second, leaving you with what you want), the problem is that you're looking for *any* character in a series that passes for the entire string to pass, so any strings with individual characters that pass each criteria make it through.