Double substring-CodePudding

in my table, I have a column that contains two IDs:

example:

Comment
Internal message ID: 7878 External message ID: 788CBNCGD9_3_5_87BFHJD
Internal message ID: 90278 External message ID: 788H2692029_3_5_8890ZG

I want to get two columns: Internal Id and External ID

Internal ID	External ID
7878	788CBNCGD9_3_5_87BFHJD
90278	788H2692029_3_5_8890ZG

I’ve been thinking about using the substring but I don’t know how.

Can someone help me?

CodePudding user response：

Honestly, I would suggest fixing your design at source, and not inserting what is essentially delimited data into your database that you then want to consume.

Saying that, however, on the sample data we have you can achieve this with SQL Server's rudimentary string manipulation:

SELECT SUBSTRING(V.Comment,CI1.I,CI2.I-CI1.I),
       SUBSTRING(V.Comment,CI3.I, ISNULL(NULLIF(CI4.I,0),LEN(V.Comment) 1)  - CI3.I)
FROM (VALUES('Internal message ID: 7878 External message ID: 788CBNCGD9_3_5_87BFHJD'),
            ('Internal message ID: 90278 External message ID: 788H2692029_3_5_8890ZG'))V(Comment)
     CROSS APPLY (VALUES(CHARINDEX(':',V.Comment) 2))CI1(I)
     CROSS APPLY (VALUES(CHARINDEX(' ',V.Comment,CI1.I)))CI2(I)
     CROSS APPLY (VALUES(CHARINDEX(':',V.Comment,CI2.I) 2))CI3(I)
     CROSS APPLY (VALUES(CHARINDEX(' ',V.Comment,CI3.I)))CI4(I);