MySQL - Search for bracket and space in string

Question

I have a database with names required to be in this format: FIRST LAST [ID NUMBER]

These names are entered manually and are sometimes entered incorrectly without the second space like this: FIRST LAST[ID NUMBER]

My intent is to search for all instances of the incorrect format. I'm trying to find a way to search the string for an expression containing:

• a left bracket immediately preceded by a letter rather than the required space
• only one space (or two, which I can use with NOT LIKE)
• a space followed by a left bracket, which I can use with NOT LIKE

I've searched extensively, and I've tried several commands such as RLIKE, REGEXP, NOT LIKE, SPACE, etc. I can't get anything to work. Here is just one example I've tried which returns both correct and incorrect entries:

SELECT CO.name 'User'
FROM tcompany CO
WHERE CO.name NOT LIKE '%. \\['
ORDER BY CO.name ASC;

Answer 1

I think this might help you. you can actually search NOT for your desired string and the leftovers are your records that match.

SELECT CO.name 'User'
FROM tcompany CO
WHERE CO.name NOT LIKE '(Select CONCAT(FIRST, " " ,LAST) from User) [%%]'
ORDER BY CO.name ASC;

Answer 2

Another option specifically to find missing spaces before the left bracket:

WHERE SUBSTR(CO.name, LOCATE('[', CO.name) - 1, 1) <> ' '

And if you want to correct them:

UPDATE tcompany
SET name = REPLACE(name, '[', ' [')
WHERE SUBSTR(name, LOCATE('[', name) - 1, 1) <> ' '

Answer 3

This was interesting. So to meet your first condition the following will work.

SELECT CO.name 'User'
FROM tcompany CO
WHERE CO.name NOT LIKE '%[ ][[]%'
ORDER BY CO.name ASC;

Answer 4

Use:

WHERE CO.name NOT LIKE '% [%'