CODEWITHSUNDEEP
mysqlsqlregexmatch
Gagantous
Gagantous

Reputation: 518

How to include whitespace but exclude any charater except number in regex mysql?

I have table like this

NO1                 | NO2
3173 02241016 0008 | 3673 0619 0590 0001
3173 0219 0312 1023 | 3671 1101 0380 0007
3173022701090232    | 3173 0218 0583 0006
3173 022601097060   | 3173 0214 0278 0010
3173021201121012    | 3173 0259 0694 1002
anywords any        | anywords any2
anyword2any2        | anywordsany3
anywords/ any-@       | anywords any2

I want to search the NO1 column if the value contain whitespace or any kind of seperated value like (\r,\n,\t) so i used this

select * from table1 where NO1 REGEXP '[[:blank:]]'

the result was

    NO1                 | NO2
    3173 02241016 0008  | 3673 0619 0590 0001
    3173 0219 0312 1023 | 3671 1101 0380 0007
    3173 022601097060   | 3173 0214 0278 0010
    anywords any        | anywords any2
    anywords/ any-@       | anywords any2

Now i want to exclude at least 1 kind of word except number but i don't know how to combine include and exclude inside a regex match query

    NO1                 | NO2
    3173 02241016 0008  | 3673 0619 0590 0001
    3173 0219 0312 1023 | 3671 1101 0380 0007
    3173 022601097060   | 3173 0214 0278 0010

How could i do that ?

Upvotes: 0

Views: 68

Answers (2)

Rick James
Rick James

Reputation: 142218

where  NO1 regexp '^[0-9]+[[:blank:]][[:blank:]0-9]*$'

is probably faster than Gordon's version.

This might be needed if you must avoid trailing whitespace:

where  NO1 regexp '^[0-9]+[[:blank:]][[:blank:]0-9]*[0-9]$'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269573

You seem to want rows that consist of numbers at at least one blank. A simple method is to fine any row that doesn't contain these characters:

where NO1 NOT regexp '[^[:blank:]0-9]' AND NO1 REGEXP '[[:blank:]]'

or:

where  NO1 regexp '^[[:blank:]0-9]+[[:blank:]][[:blank:]0-9]*$'

Here is a db<>fiddle.

Upvotes: 1

Related Questions