locked
Return all special characters in column RRS feed

  • Question

  • I thought I knew how to do this but I am not getting it. I am using this.

    select * from data  where random LIKE '%[^0-9A-Z]%'


    Alan

    Monday, March 21, 2016 6:31 PM

Answers

  • I assume you don't want to to count spaces as a special character.  That would make your where clause (note the added space in the brackets

    where lower(random) LIKE '%[^0-9a-Z ]%'

    If you had that only the "Acropo ..." row would be returned (because of the " and ,).  If you also wanted " and , to not be counted as a special character, then

    where lower(random) LIKE '%[^0-9a-Z ,"]%'

    In general, any character you don't want to be a special character must be included in the brackets (either explicitly or in a range like 0-9).

    Tom

    • Marked as answer by anaylor01 Tuesday, March 22, 2016 1:46 AM
    Tuesday, March 22, 2016 1:30 AM
  • If you mean you want to return all rows with any character except 0-9 and A-Z, the above seems to work for me (if, that is, you are using a case insensitive, accent insensitive collation).  For example

    create table data(random varchar(20));
    insert data(random) Values (''), ('9!'), ('0'), ('B12'), ('l'), ('@'),('a>9')
    select * from data  where random LIKE '%[^0-9A-Z]%' 
    /* returned result
    random
    --------------------
    9!
    @
    a>9
    */
    
    

    Could you give us more information on what you mean by "return all special characters in column", what collation you are using and sample data which does not work for you?

    Tom

    • Proposed as answer by Naomi N Monday, March 21, 2016 7:08 PM
    • Marked as answer by anaylor01 Monday, March 21, 2016 8:41 PM
    Monday, March 21, 2016 7:05 PM

All replies

  • That will return rows with a special character in the random column, yes.

    DECLARE @textTable TABLE (string NVARCHAR(20))
    INSERT INTO @textTable ( string )
    VALUES  ('$%^&*'),('abng'),('abng!')
    
    SELECT *
      FROM @textTable
     WHERE string LIKE '%[^0-9A-Z]%'

    If you want to return ONLY special characters that's a bit different.

    Here's a modified function that might help:

    CREATE FUNCTION dbo.notAlphaNumeric(@inputString NVARCHAR(MAX))
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
    DECLARE @outputString NVARCHAR(MAX) = ''
     WHILE LEN(@inputString) > 0
     BEGIN
      SET @outputString = @outputString + CASE WHEN ASCII(LEFT(@inputString,1)) NOT BETWEEN 48 AND 57 
                                                 AND ASCII(LEFT(@inputString,1)) NOT BETWEEN 65 AND 90 
    											 AND ASCII(LEFT(@inputString,1)) NOT BETWEEN 97 AND 122 THEN LEFT(@inputString,1) ELSE '' END
      SET @inputString = RIGHT(@inputString,LEN(@inputString)-1)
    
     END
     RETURN @outputString
    END

    DECLARE @textTable TABLE (string NVARCHAR(20))
    INSERT INTO @textTable ( string )
    VALUES  ('$%^&*'),('abng'),('abnBg!')
    
    SELECT *, dbo.notAlphaNumeric(string)
      FROM @textTable
     WHERE string LIKE '%[^0-9A-Z]%'


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Proposed as answer by Naomi N Monday, March 21, 2016 7:08 PM
    Monday, March 21, 2016 7:04 PM
  • If you mean you want to return all rows with any character except 0-9 and A-Z, the above seems to work for me (if, that is, you are using a case insensitive, accent insensitive collation).  For example

    create table data(random varchar(20));
    insert data(random) Values (''), ('9!'), ('0'), ('B12'), ('l'), ('@'),('a>9')
    select * from data  where random LIKE '%[^0-9A-Z]%' 
    /* returned result
    random
    --------------------
    9!
    @
    a>9
    */
    
    

    Could you give us more information on what you mean by "return all special characters in column", what collation you are using and sample data which does not work for you?

    Tom

    • Proposed as answer by Naomi N Monday, March 21, 2016 7:08 PM
    • Marked as answer by anaylor01 Monday, March 21, 2016 8:41 PM
    Monday, March 21, 2016 7:05 PM
  • I used this select * from data3  where lower(random) LIKE '%[^0-9a-Z]%' and it returned records with special characters. But it also returned records that don't have any special characters. Why is that?

    Alan

    Monday, March 21, 2016 8:42 PM
  • Please post an example of the strings returned which did not contain the special characters.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, March 21, 2016 8:48 PM
  • Aaron Cross
    "Acropolis, Greece",Abbott and Costello
    Abraham Lincoln
    Ace Ventura
    Action Jackson
    Agent Rupert Anderson
    Agent Smith
    Ah Jong

    Alan

    Tuesday, March 22, 2016 1:18 AM
  • I assume you don't want to to count spaces as a special character.  That would make your where clause (note the added space in the brackets

    where lower(random) LIKE '%[^0-9a-Z ]%'

    If you had that only the "Acropo ..." row would be returned (because of the " and ,).  If you also wanted " and , to not be counted as a special character, then

    where lower(random) LIKE '%[^0-9a-Z ,"]%'

    In general, any character you don't want to be a special character must be included in the brackets (either explicitly or in a range like 0-9).

    Tom

    • Marked as answer by anaylor01 Tuesday, March 22, 2016 1:46 AM
    Tuesday, March 22, 2016 1:30 AM
  • Hello 

    Try this , This only give column with special character.

    DECLARE @textTable TABLE (string NVARCHAR(20))
    INSERT INTO @textTable ( string )
    VALUES  ('$%^&*'),('ab96ng@@'),('abn0g!'),
    ('Abbott and Costello'),('"Acropolis, Greece"'),('#@')
    
    SELECT *
      FROM @textTable
     
     WHERE PATINDEX('%[0-9A-Za-z]%',string)= 0
    Let me know your feedback !!

    Tuesday, March 22, 2016 5:10 AM