Create a string in MySQL using an input parameter

Question

Having an input parameter, for example num = 3, I want to create a string in MySQL, concatenated by ','.

In Python it would look like this:

num = 3

string = ['target_table']
for i in range(num):
    string.append('hub_hid_column_' + str(i))
    string.append('hub_hid_column_name_' + str(i))

print(','.join(string))

At the end i want to have the following output: target_table,hub_hid_column_0,hub_hid_column_name_0,hub_hid_column_1,hub_hid_column_name_1,hub_hid_column_2,hub_hid_column_name_2

How can i do it in MySQL? Ideally without a procedure.

Bill Karwin · Accepted Answer · 2022-01-17 15:04:11Z

1

Here's a solution:

WITH RECURSIVE num AS (
  SELECT 1 AS n
  UNION
  SELECT n+1 FROM num WHERE n < 3 -- here is where you change your number
)
SELECT CONCAT('target_table,', 
  GROUP_CONCAT(CONCAT('hub_hid_column_',n-1,',hub_hid_column_name_',n-1))) AS list
FROM num;

Output:

+-----------------------------------------------------------------------------------------------------------------------------------+
| list                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------+
| target_table,hub_hid_column_0,hub_hid_column_name_0,hub_hid_column_1,hub_hid_column_name_1,hub_hid_column_2,hub_hid_column_name_2 |
+-----------------------------------------------------------------------------------------------------------------------------------+

While SQL is great for certain tasks, other languages may be better suited to this task.

edited Jan 17, 2022 at 15:04

answered Jan 17, 2022 at 14:45

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniil Yefimov Over a year ago

Awesome! Thank you!

Collectives™ on Stack Overflow

Create a string in MySQL using an input parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related