How to search for results in MySQL where a particular field has only special characters?

1

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?

    
asked by anonymous 07.03.2016 / 19:10

4 answers

0

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.

    
09.03.2016 / 01:13
2

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.

    
08.03.2016 / 15:19
1
^([[:punct:]]|[[:cntrl:]]|[[:blank:]])+$

Match all fields that contain only punctuation or control characters or spaces / line breaks, following fiddle contains an example usage.

    
07.03.2016 / 19:27
0

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

    
07.03.2016 / 19:27