Return SQL Rows Where Field Contains Only Non-Alphanumeric Characters

Return sql rows where field contains ONLY non-alphanumeric characters

SQL Server doesn't have regular expressions. It uses the LIKE pattern matching syntax which isn't the same.

As it happens, you are close. Just need leading+trailing wildcards and move the NOT

 WHERE whatever NOT LIKE '%[a-z0-9]%'

Return rows where field contains non alpha-numeric characters

The question you are referring to is for SQL Server. To do what you want in SQL Server, you would do:

where col like '%[^a-zA-Z0-9]%'

The ^ in the like pattern matches any characters not in the list.

SQL Server 2008 query to find rows containing non-alphanumeric characters in a column

Won't this do it?

SELECT * FROM TABLE
WHERE COLUMN_NAME LIKE '%[^a-zA-Z0-9]%'

Setup

use tempdb
create table mytable ( mycol varchar(40) NULL)

insert into mytable VALUES ('abcd')
insert into mytable VALUES ('ABCD')
insert into mytable VALUES ('1234')
insert into mytable VALUES ('efg%^&hji')
insert into mytable VALUES (NULL)
insert into mytable VALUES ('')
insert into mytable VALUES ('apostrophe '' in a sentence') 

SELECT * FROM mytable
WHERE mycol LIKE '%[^a-zA-Z0-9]%'

drop table mytable 

Results

mycol
----------------------------------------
efg%^&hji
apostrophe ' in a sentence

How can I search for rows that contain a non-alphanumeric or space character?

How about you add the space:

SELECT *
FROM myTable
WHERE myField LIKE '%[^a-zA-Z0-9 ]%'

SELECT only rows that contain only alphanumeric characters in MySQL

Try this code:

SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$'

This makes sure that all characters match.

How can I select all records with non-alphanumeric and remove them?

I suggest using REGEXP_REPLACE for select, to remove the characters, and using REGEXP_CONTAINS to get only the one you want.

SELECT REGEXP_REPLACE(EMPLOYER, r'[^a-zA-Z\d\s]', '') 
FROM fec.work
WHERE REGEXP_CONTAINS(EMPLOYER, r'[^a-zA-Z\d\s]')

You say you don't want to use replace because you don't know how many alphanumerical there is. But instead of listing all non-alphanumerical, why not use ^ to get all but alphanumerical ?

EDIT :

To complete with what Mikhail answered, you have multiple choices for your regex :

'[^a-zA-Z\\d\\s]'  // Basic regex
r'[^a-zA-Z\d\s]'   // Uses r to avoid escaping
r'[^\w\s]'         // \w = [a-zA-Z0-9_] (! underscore as alphanumerical !)

If you don't consider underscores to be alphanumerical, you should not use \w

Properly select actual non-alphanumeric characters in MySQL column

Have you tried:

SELECT * FROM table WHERE name REGEXP "[^a-z0-9]+" LIMIT 0,10;

---

Related Topics