Need to extract data between delimiter in a column and string split into row based on unique id

Question

Hello,

I have the following table:
Unique ID Desc Owner Name
123 aaaaaa Twain Mark J.(12345) Kung, Fu P(kung.j)
345 bbbbb Margaret Susan J(22222) Cruise, Tom(66666) Hamilton Lo (U1234)

I need the table in the following way: (getting the id in the round brackets of the Owner name field split into different row)
Unique ID Desc Owner ID
123 aaaaaa 12345
123 aaaaaa kung.j
345 bbbbb 22222
345 bbbbb 66666
345 bbbbb U1234

I tried cross apply string split but did not work. Could someone please help.

Thank you.

Answer 1

Hi @RIDDHI ,

Please try the following solution.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (UniqueID INT PRIMARY KEY, [DESC] VARCHAR(100), OwnerName VARCHAR(100));  
INSERT INTO @tbl (UniqueID, [DESC], OwnerName) VALUES  
(123, 'aaaaaa', 'Twain Mark J.(12345) Kung, Fu P(kung.j)'),  
(345, 'bbbbb', 'Margaret Susan J(22222) Cruise, Tom(66666) Hamilton Lo (U1234)');  
-- DDL and sample data population, end  
  
SELECT * FROM @tbl;  
  
SELECT tbl.*  
	, OwnerID = LEFT(t1.value, t2.pos - 1)  
FROM @tbl AS Tbl   
   CROSS APPLY STRING_SPLIT(Tbl.OwnerName,'(') As t1  
   CROSS APPLY (SELECT CHARINDEX(')', t1.value) ) As t2(pos)  
WHERE t1.value LIKE '%)%';  

Output

+----------+--------+----------------------------------------------------------------+---------+  
| UniqueID |  DESC  |                           OwnerName                            | OwnerID |  
+----------+--------+----------------------------------------------------------------+---------+  
|      123 | aaaaaa | Twain Mark J.(12345) Kung, Fu P(kung.j)                        | 12345   |  
|      123 | aaaaaa | Twain Mark J.(12345) Kung, Fu P(kung.j)                        | kung.j  |  
|      345 | bbbbb  | Margaret Susan J(22222) Cruise, Tom(66666) Hamilton Lo (U1234) | 22222   |  
|      345 | bbbbb  | Margaret Susan J(22222) Cruise, Tom(66666) Hamilton Lo (U1234) | 66666   |  
|      345 | bbbbb  | Margaret Susan J(22222) Cruise, Tom(66666) Hamilton Lo (U1234) | U1234   |  
+----------+--------+----------------------------------------------------------------+---------+