sql split column in columns by delimiter

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:

I have seen many similar questions but a lot seemed outdated (like before 2016, where apparently a split function was introduced) but from what I’ve seen the split function mainly splits a specific string and not a column.

Thanks a lot for any help!!

>Solution :

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