Extract multiple instances of text from a MySQL column restricted to certain characters

Question

I would like to extract some fragments from a MySQL column that are bounded in some unique way. Here is an example:

I have the following table (Table A):

id | column_a
---+---------
1  | There is a user [u?u=990] and another [u?u=5458855].
2  | And here is just one user [u?u=9390].
3  | And here is nothing.

And I want to get the following:

id | result_a
---+---------
1  | 990
1  | 5458855
2  | 9390

At the moment, I have this query:

SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(column_a, '[u?u=', -1), ']', 1) AS result_a FROM TableA

But it gives me:

id | result_a
---+---------
1  | 5458855
2  | 9390

How can I improve this query?

Thanks!

Answer 1

This problem arose because your circuit breaks 1NF .

( , , , "", , ), column_a , , - .

, ( ) , & hellip;

SQL : , MySQL, - JOIN. , , ,

SELECT id, SUBSTRING_INDEX(
             SUBSTRING_INDEX(column_a, '[u?u=', -1 - n.v)
           , ']', 1) AS result_a
FROM   TableA JOIN (
         SELECT 0 v
       UNION ALL
         SELECT 1
       ) n

, :

• , . , :

  WHERE RIGHT(column_a, 5+CHAR_LENGTH(
          SUBSTRING_INDEX(column_a, '[u?u=', -1 - n.v)
        )) REGEXP '^\\[u\\?u=.*\\]'
  

• , , , 100, , , , , , ( 128, WHERE n , ):

  SELECT id, SUBSTRING_INDEX(
               SUBSTRING_INDEX(column_a, '[u?u=', -1 - n.v)
             , ']', 1) AS result_a
  FROM   TableA JOIN (
           SELECT b6.v | b5.v | b4.v | b3.v | b2.v | b1.v | b0.v AS v
           FROM   (
                    SELECT 0 v UNION ALL SELECT 1<<0
                  ) b0 JOIN (
                    SELECT 0 v UNION ALL SELECT 1<<1
                  ) b1 JOIN (
                    SELECT 0 v UNION ALL SELECT 1<<2
                  ) b2 JOIN (
                    SELECT 0 v UNION ALL SELECT 1<<3
                  ) b3 JOIN (
                    SELECT 0 v UNION ALL SELECT 1<<4
                  ) b4 JOIN (
                    SELECT 0 v UNION ALL SELECT 1<<5
                  ) b5 JOIN (
                    SELECT 0 v UNION ALL SELECT 1<<6
                  ) b6
         ) n
  WHERE  RIGHT(column_a, 5+CHAR_LENGTH(
           SUBSTRING_INDEX(column_a, '[u?u=', -1 - n.v)
         )) REGEXP '^\\[u\\?u=.*\\]'
  

• MySQL , .

Answer 2

. 1 1 .

REGEXP RegExp MySQL

MySQL, , , .