SELECT records that contains just ENGLISH A - Z 1 -9 charachters I have a non English language website. Recently someone published a lot of English spammy topics.

I need a SQL statement to select topics that contains only English language.

Knowing that some of our original (not spammy) website topics contains some phrases in English we do not want to bring those for us.

I tried:

SELECT * FROM `posts` WHERE `title` NOT LIKE '[^\x20-\x7E]' 


The result was all posts.

For example:


"Where are you from?"    ---> Only English NOT ALLOWED 
"من اين انت؟"                     ---> Only Arabic ALLOWED
"مامعنى كلمة where بالانكليزي"  --> English &amp; Arabic ALLOWED


We just need to browse which like the no. 1 without results like no. 3
For MySQL you can use REGEXP

Like

 SELECT * FROM table WHERE column REGEXP '^[[:alnum:]]+$';


Or if you need to exclude some letters

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


SQL Server has a similar Expression like2

SELECT * FROM table WHERE column NOT LIKE '%[^ ,.-~]%' collate Latin1_General_BIN


DEMO für ,ysql



CREATE TABLE Demo (ID INT NOT NULL AUTO_INCREMENT
, Name VARCHAR(50) NOT NULL,
     PRIMARY KEY (ID));

INSERT INTO Demo (Name)

SELECT '0123456789'

UNION ALL

SELECT 'abcdefghijklmnopqrstuvwxyz'

UNION ALL

SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

UNION ALL

SELECT '0.a'

UNION ALL

SELECT '0,a'

UNION ALL

SELECT '0éa'

UNION ALL

SELECT '0!a'

UNION ALL

SELECT '0?a'

UNION ALL

SELECT '0''a'

UNION ALL

SELECT '0#a'

UNION ALL

SELECT '0+a'



✓

✓






SELECT * FROM Demo  WHERE Name REGEXP '^[A-Za-z0-9]+$';



ID | Name                      
-: | :-------------------------
 1 | 0123456789                
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ






SELECT * FROM Demo



ID | Name                      
-: | :-------------------------
 1 | 0123456789                
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ
 4 | 0.a                       
 5 | 0,a                       
 6 | 0éa                      
 7 | 0!a                       
 8 | 0?a                       
 9 | 0'a                       
10 | 0#a                       
11 | 0+a                       



db&lt;>fiddle here

SQL SERVEr Example




CREATE TABLE #Demo (ID INT NOT NULL IDENTITY(1,1), Name VARCHAR(50) NOT NULL)
INSERT INTO #Demo (Name)

SELECT '0123456789'

UNION ALL

SELECT 'abcdefghijklmnopqrstuvwxyz'

UNION ALL

SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

UNION ALL

SELECT '0.a'

UNION ALL

SELECT '0,a'

UNION ALL

SELECT '0éa'

UNION ALL

SELECT '0!a'

UNION ALL

SELECT '0?a'

UNION ALL

SELECT '0''a'

UNION ALL

SELECT '0#a'

UNION ALL

SELECT '0+a'
GO



11 rows affected






SELECT * FROM #Demo WHERE Name NOT LIKE '%[^ ,.-~]%' collate Latin1_General_BIN
GO



ID | Name                      
-: | :-------------------------
 1 | 0123456789                
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ
 4 | 0.a                       
 5 | 0,a                       
 8 | 0?a                       






SELECT * FROM #Demo
GO



ID | Name                      
-: | :-------------------------
 1 | 0123456789                
 2 | abcdefghijklmnopqrstuvwxyz
 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ
 4 | 0.a                       
 5 | 0,a                       
 6 | 0éa                      
 7 | 0!a                       
 8 | 0?a                       
 9 | 0'a                       
10 | 0#a                       
11 | 0+a                       



db&lt;>fiddle here