InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 06:21:12 GMT20http://www.sqlservercentral.com/Forums/Topic824139-8-1.aspxThanks a lot!!!Tue, 24 Nov 2009 21:30:19 GMTshashi_1409http://www.sqlservercentral.com/Forums/Topic824139-8-1.aspxThe 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[code="sql"][UserName] LIKE '%[^a-z]%' AND[/code]This will pull in anything with a character other than a-z. So any string with a number passes this test. [code="sql"][UserName] LIKE '%[^0-9]%' AND[/code]This will pull in anything with a character other than 0-9, so anything with a letter passes this test.[code="sql"][UserName] LIKE '%[^A-Z]%'[/code]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.Tue, 24 Nov 2009 20:36:23 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic824139-8-1.aspxI had the general idea right, just the implementation wrong.Try this:[code="sql"]SELECT *FROM#frUser_Copy WHERE[UserName] LIKE '%[^a-z0-9]%'[/code]Unless you have a case-sensitive collation then the a-z will work for any alpha characters including upper-caseTue, 24 Nov 2009 16:17:00 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic824139-8-1.aspxJack 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 issuehere is my test dataIF 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]%'Tue, 24 Nov 2009 15:54:14 GMTshashi_1409http://www.sqlservercentral.com/Forums/Topic824139-8-1.aspxgreat !!Thanks Jack!Tue, 24 Nov 2009 15:30:58 GMTshashi_1409http://www.sqlservercentral.com/Forums/Topic824139-8-1.aspxHow about something like this:[code="sql"]/*Set up test table and data */IF OBJECT_ID('dbo.#test') IS NOT NULL BEGIN DROP TABLE #test END CREATE TABLE #test ( test_col VARCHAR(10) ) DECLARE @int INTEGER, @string VARCHAR(10) SET @int = 0WHILE @int <=223 BEGIN IF LEN(@string) < 10 OR @string IS NULL BEGIN SET @string = ISNULL(@string, '') + CHAR(FLOOR(RAND(@int) * @int + 1)) END ELSE Begin INSERT INTO #test SELECT @string SET @string = '' END SET @int = @int + 1 END/*Return all the rows*/SELECT * FROM #test AS T/*Return the rows that return non-alphanumeric characters.*/SELECT *FROM #test AS TWHERE test_col LIKE '%[^a-z]%' AND test_col LIKE '%[^0-9]%' AND test_col LIKE '%[^A-Z]%'[/code]Tue, 24 Nov 2009 15:18:52 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic824139-8-1.aspxany characters other than [a-z],[A-Z],[0-9]Tue, 24 Nov 2009 14:54:42 GMTshashi_1409http://www.sqlservercentral.com/Forums/Topic824139-8-1.aspxWhat do you mean by special characters?Tue, 24 Nov 2009 14:34:57 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic824139-8-1.aspxHi, I am trying to list [b]usernames [/b] with special characters from user table. I have tried using [b]like[/b] operator, but I dont have the complete list of special characters . so I would really appreciate if some one can help me out with the query. or help me with the query that lists all special characters.thanksTue, 24 Nov 2009 14:04:04 GMTshashi_1409