【Question】

I’d like to extract certain snippets from a MySQL column which are bounded in some unique way. Here is an example:

I have the following table (TableA):

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 this:

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 that gives me:

id | result_a

—+———

1 | 5458855

2 | 9390

This is an solution someone offered, but I’m not sure whether it works or not:

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=.*\\]’

 

【Answer】
This involves both string splitting and one-row to multiple-row conversion. It’s hard to handle these in SQL. You can make it easy with SPL. Here’s SPL script:

 

	A
1	$select id,column_a from   TableA
2	=A1.run(column_a=column_a.split@b("u?u=").to(2,).(parse@n   (~)))
3	=A2.news(column_a; A2.id:id,   ~:result_a)

A1: Retrieve data from TableA;

A2: Split away a sequence of substring(s) from each column_a value;

A3: Generate the new desired table sequence;