How to search for results in Mysql where a given field only has special characters?

Asked

Viewed 1,429 times

1

I need to search in Mysql, lines where a given field, for example 'Name', is filled only with special characters or white spaces.

I tried several REGEX for such but unsuccessful.

Could someone help me?

  • You need it to contain some special character or special character only

4 answers

2

  • Problems: only captures fields that contain exactly one character ; does not take into account Unicode, so will consider is, ç, á, etc as special character ; if you fix the problem of picking only one character still you will get fields that contain at least 1 special character or white space (for example "Iron Man") and the question description says that are fields that contain only these.

  • 1

    @Brunorb as to one character only, in fact (he missed the + after the group), but the example of the "Iron Man" with space does not proceed. Has start and end line marking.

  • @True Bacco, my mistake. Also in the question it is not specified if Unicode is necessary then if Iron add the + missing in the regex I withdraw the downvote.

  • Brunorb thanks for highlighting the problem. is what to give doing things in a hurry and Bacco thanks for the credit.

1

^([[:punct:]]|[[:cntrl:]]|[[:blank:]])+$

Will match all fields that only contain scores or control characters or spaces/line breaks, the following fiddle contain an example of use.

0


Search for names that do not have alphanumeric characters.

Example:

SELECT `name` from `person` where `name` REGEXP "^[^[:alnum:]]+$"

The advantage in the use of this expression: ^[^[:alnum:]]+$ is that 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 return only ( , ..

Other rules can return valid names. Example, [ a-Z0-9]+$ returns:

( , .
大嶺
محمد

Yet it doesn’t guarantee total integrity.

You can have cases where there are characters like this  . Note that it appears to be a common space character, but 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 such cases, when a rule does not detect. A specific search should be made only for these special cases or specific rules that can find the special sets.

0

I did this on SQL Server and functional

EXAMPLE TABLE AND EXAMPLE QUERY

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

Browser other questions tagged

You are not signed in. Login or sign up in order to post.