Double substring

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

example:

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

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

Can someone help me?

>Solution :

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);