[SOLVED] MySQL: How to select and find ids in bracket [id] list

Issue

I have a large database, how can I find identifiers in this example of a database column?

Find in text column [bracket ids]?

SELECT * FROM `explanations` WHERE `id_Verse` REGEXP in '[1007],[991]'

This is a sample query, please suggest a valid query for this sample, select WHERE text.

Solution

One approach is to simplify the expression:

\\[(991|1007)\\]

As opposed to defining each set of brackets, the above expression finds the set of digits surrounded in brackets [] that equals 991 or 1007.
The () parentheses is used to group explicit digit sets while | works as an or operator between each set.
The backslash \ is used to escape the brackets so they are considered as literal instead of as a regular expression character set match eg: [a-zA-Z0-9] to match alpha-numeric or [bfs]ad to match bad, fad, or sad.
In MySQL queries you need to use two backslashes \ for it to be represented as a literal backslash, as backslash is often used as the escape sequence character for example 'Your\'s'.

SELECT * 
FROM `explanations`
WHERE `id_Verse` REGEXP '\\[(991|1007)\\]';

See working db<>fiddle example.

CREATE TABLE foo(
  `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `id_Verse` TEXT
);

INSERT INTO foo(id_Verse)
VALUES 
   ('[991]'), /* <--- */
   ('[1070]'),
   ('[991][992][1004][1007]'), /* <--- */
   ('[991][992][1004][1007][1010]'), /* <--- */
   ('[1007][1008]'), /* <--- */
   ('[989][991]'), /* <--- */
   ('[9910]'),
   ('[964]'),
   ('[1007]'); /* <--- */

Answered By – Will B.

Answer Checked By – Candace Johnson (BugsFixing Volunteer)