I have a large database, how can I find identifiers in this example of a database column? find in text column [bracket id's]

    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 **

CodePudding user response：

This is a very odd structure in my opinion, but if you want to find every row that contains [1007] or [991] I would probably use the LIKE operator with the % wildcard.

Example:

SELECT * 
FROM `explanations` 
WHERE `id_Verse` like '%[991]%' or `id_Verse` like '%[1007]%'

Not sure if this is what you're looking for though. https://www.mysqltutorial.org/mysql-like/

CodePudding user response：

There are multiple possibilities besides LIKE

But a simpler solution would be normalize your table and use bridge table to store the ids

CREATE TABLe explanations (id_Verse vARCHAR(100),
       FULLTEXT idx (id_Verse))

INSERT INTO explanations VALUES ('[1007][991]'),('[991][1008]'),('[1006][1007][1008]'),('[1010]')

using rEGEXP

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

| id_Verse           |
| :----------------- |
| [1007][991]        |
| [991][1008]        |
| [1006][1007][1008] |

using FULL TEXT SEARCH

SELECT * FROM `explanations` WHERE MATCH(id_Verse) AGAINST('[1007]' '[991]')

| id_Verse           |
| :----------------- |
| [1007][991]        |
| [991][1008]        |
| [1006][1007][1008] |

db<>fiddle here

CodePudding user response：

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.

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]'); /* <--- */

id_Verse
[991]
[991][992][1004][1007]
[991][992][1004][1007][1010]
[1007][1008]
[989][991]
[1007]