sql split column in columns by delimiter

Question

I have a SQL table in a SQL database in Azure with the column id which has entries in the form AAA_1, AAA_2, BBB_1, BBB_2.... I would like to split this column in two columns by the delimiter _ like this:

id	…
AAA_1	…
AAA_2	…
BBB_2	…

id1	id2	…
AAA	1	…
AAA	2	…
BBB	2	…

The split function mainly splits a specific string and not a column.

Answer 1

use SPLIT_PART() to separate the column based on delimiter "_"

SELECT
  id,
  SPLIT_PART(id, '_', 1) AS id1,
  SPLIT_PART(id, '_', 2) AS id2
FROM your_table_name;

• SPLIT_PART() takes 3 args(column you want to split (id) + delimiter ('_') + position of the substring)

update this solution is supported by a wide range of SQL databases such as PostgreSQL/MySQL/SQLite...

If you are working with SQL Server you can try PARSENAME()

SELECT
  id,
  PARSENAME(REPLACE(id, '_', '.'), 2) AS id1,
  PARSENAME(REPLACE(id, '_', '.'), 1) AS id2
FROM your_table_name;

• REPLACE() will replace _ with . so that PARSENAME() can split the string

Ps. check documentations for your DB to see if SPLIT_PART() is supported and equivalent functions