I need to search in MySQL, lines where a particular field, for example 'Name', is filled with only special characters or spaces.
I tried several REGEX for this, but without success.
Could anyone help me?
I need to search in MySQL, lines where a particular field, for example 'Name', is filled with only special characters or spaces.
I tried several REGEX for this, but without success.
Could anyone help me?
Look for names that do not have alphanumeric characters.
Example:
SELECT 'name' from 'person' where 'name' REGEXP "^[^[:alnum:]]+$"
The advantage of using this expression: ^[^[:alnum:]]+$
is that it identifies multibyte characters as being valid within the alphanumeric rule.
Example
CREATE TABLE 'person' (
'id' int(5) NOT NULL,
'name' char(100) NOT NULL
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO 'person' VALUES
(1, 'john doe'),
(2, '( , .'),
(3, '大嶺'),
(4, 'محمد');
The rule ^[^[:alnum:]]+$
will only return ( , .
.
Other rules may return valid names. Example, ^ [^ a-z0-9] + $ returns:
( , .
大嶺
محمد
It still does not guarantee full integrity.
There may be instances where there are characters like this
. Note that this appears to be a common space character, however it is multibyte, different from . Notice the difference in size.
It is common to find names with emoji. Example: (´・ω・
) '. Which are also not detected by this rule.
In these cases, when a rule does not detect. A specific search should only be made for those special cases or specific rules that can find the special sets.
Try this:
SELECT * FROM Table_Name WHERE nome REGEXP ^[^a-z0-9]+$
I removed the A-Z
, since it is only necessary to use the BINARY
operator to be case sensitive.
this link: link has a list of unicode characters to customize the query its way.
^([[:punct:]]|[[:cntrl:]]|[[:blank:]])+$
Match all fields that contain only punctuation or control characters or spaces / line breaks, following fiddle contains an example usage.
I did this in SQL Server and functional
TABLE EXAMPLE AND EXAMPLE EXAMPLE
CREATE TABLE #Sample(Field varchar(50), Result varchar(50))
GO
INSERT INTO #Sample (Field, Result) VALUES ('ABC123 ', '1')
INSERT INTO #Sample (Field, Result) VALUES ('ABC123.', '2')
INSERT INTO #Sample (Field, Result) VALUES ('ABC123&', '3')
INSERT INTO #Sample (Field, Result) VALUES ('&*¨*¨*(', '4')
INSERT INTO #Sample (Field, Result) VALUES ('&* &^^>*¨*(', '5')
INSERT INTO #Sample (Field, Result) VALUES ('&* daniel&^^>*¨*(', '6')
SELECT * FROM #Sample WHERE Field NOT LIKE N'%[0-9A-Za-z]%' collate Latin1_General_BIN
GO
DROP TABLE #Sample
See if this works for MySQL