How to find Foreign character in a content using PHP?

Question

I have column details in one table. that column stores email contents in HTML format. column data type is blob . my requirement to search and find any email content contains non English characters ie foreign languages.

The table with 51000 records. In 51000 records i need filter only email with non -English characters. it may be 100 or greater than that .once i filter those records i will manually identify the languages using Google translator

Take a white-list approach. Find the ones that contain something other than your list of acceptable characters. Trying to enumerate all the ones you consider 'foreign' would be daunting. Could use PCRE right in your SQL to get this. — ficuscr, Feb 20 '13 at 18:28
To expand on that, use a regular expression to find values that match non-acceptable characters, and any emails that are matched by that regular expression thus must be acted upon. — DiMono, Feb 20 '13 at 18:30
was expanding to that as you wrote it seems: glad we concur. — ficuscr, Feb 20 '13 at 18:31

score 0 · Accepted Answer · edited May 23 '17 at 12:18

In MySQL it would be something like this:

SELECT foo 
FROM bar
WHERE somecolumn REGEXP '[^\x00-\x80]+'

Matching on characters not in ASCII range 0-128.

also perhaps:

WHERE NOT HEX(COLUMN) REGEXP '^([0-7][0-9A-F])*$';

REGEXP '[^[.NUL.]-[.DEL.]]'

REGEXP '[^ -~]'.

You will need to tune the pattern to your needs.

Also a novel approach shown here: How can I find non-ASCII characters in MySQL?

WHERE columnToCheck <> CONVERT(columnToCheck USING ASCII)

How to find Foreign character in a content using PHP?

1 Answers1