Double substring

Question

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?

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Nov 08 '21 at 14:21

score 5 · Accepted Answer · answered Nov 08 '21 at 14:19

5

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

answered Nov 08 '21 at 14:19

Thom A

88,727
11
45
75

`NULLIF(..., 0)` on every `CHARINDEX` would be a good idea, just in case it can't find it – Charlieface Nov 08 '21 at 15:47
@Charlieface I did consider it, but with 2 examples, I felt I lacked information to suggest that the data didn't meet the data *could* follow a different format. – Thom A Nov 08 '21 at 15:50
IMO it's wise to *always* do so, because if there are any at all in the base table, even if they are filtered with a `WHERE` or a join, often the function calculation is pushed through to before predicate filtering, and can therefore cause an error. See eg https://stackoverflow.com/a/67259538/14868997 – Charlieface Nov 08 '21 at 16:02
I'm afraid I'm still of mind of that the *if* that is a scenario, @Charlieface, then additional examples were needed. – Thom A Nov 08 '21 at 16:04

Double substring

1 Answers1