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
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.
You can use
WHERE `number` REGEXP "[[:alpha:]].*[0-9]|[0-9].*[[:alpha:]]"
[[: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.
Answered By – Wiktor Stribiżew
Answer Checked By – Gilberto Lyons (BugsFixing Admin)