Answered by:
Return all special characters in column

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
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
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 -
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 JongAlan
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