I need to get all entries in MySQL table licenses where the number column contains BOTH letters and-CodePudding

I have a table called licenses that holds all my users professional license numbers. There is a field, lic_type that holds the official letter code for the professional license, "PN", "RN", "RT", "APRN", "EO", etcetera. There is another column in the same licenses table, number, that holds the numeric portion of the full license information, 2261234, 1234567, etcetera, but the field is not INT it is varchar(18) due to need for some strings (see later in this question). I am NOT able to change the database structure or the type of the number column. Currently, when I concat these two fields together, it should give the full license designation.

For example - if lic_type is "RN" and number is "2676612", then when they are concatenated, they produce the correct license for the individual - RN2676612. However, the database I have received contains SOME entries in number column that contain the full license, i.e. RN2676612, instead of just numbers. Sometimes the letters are not even the right code. For example, the lic-type may be "PN", but they may have entered "LPN2261123", so that I cannot search for the entry in lic_type in the number column.

I need an MySQL query that will return ANY row where number contains any letters and at LEAST one number. I must allow full letter entries in number such as STUDENT or WAITING, but they will always have NO numbers, so, any entry in number that has a letter and a number is invalid and I need to correct. This allows me to bypass all letter numbers when pulling information from fully licensed customers.

Currently, I have tried the following query (in phpMyAdmin):

SELECT * FROM `licenses` WHERE `number` REGEXP '(?=.*\d) (?=.*[A-Z])[\d|\w]*'

which I borrowed from a license plate example.

But it returns an error:

#1139 - Got error 'repetition-operator operand invalid' from regexp.

Please help!

CodePudding user response：

You can use

WHERE `number` REGEXP "[[:alpha:]].*[0-9]|[0-9].*[[:alpha:]]"

Here,

[[:alpha:]].*[0-9] - matches a letter, then any zero or more chars and then a digit
| - or (since a digit may precede a letter)
[0-9].*[[:alpha:]] - matches a digit, then any zero or more chars and then a letter.