Special characters

  • Hi, I am trying to list usernames with special characters from user table.

    I have tried using like 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.

    thanks

  • What do you mean by special characters?

  • any characters other than [a-z],[A-Z],[0-9]

  • How about something like this:

    /*

    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 = 0

    WHILE @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 T

    WHERE

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

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

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

  • great !!

    Thanks Jack!

  • 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)

    )

    Go

    INSERT 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]%'

  • I had the general idea right, just the implementation wrong.

    Try this:

    SELECT

    *

    FROM

    #frUser_Copy

    WHERE

    [UserName] LIKE '%[^a-z0-9]%'

    Unless you have a case-sensitive collation then the a-z will work for any alpha characters including upper-case

  • 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.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Thanks a lot!!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply