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?
4 answers
2
Try it like this:
SELECT * FROM Table_Name WHERE nome REGEXP ^[^a-z0-9]+$
removed the A-Z, because you only need to use the operator BINARY to be case sensitive.
this link: http://programandosemcafeina.blogspot.com.br/2007/04/caracteres-especiais-representados-em.html has a Unicode character list to customize the query your way.
-
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 mysql regex
You are not signed in. Login or sign up in order to post.
You need it to contain some special character or special character only
– Daniel Saraiva