I’m not sure if the title above is understandable. What I mean by that is, you have a string and you want to split it and put each string element into specific columns. The split element is separated by a delimiter.

When you see the example below, I hope you understand what I’m trying to do. (Spoiler alert: it has something to do with CROSS APPLY).

Here are the strings you receive, in three rows:

'Hauptstrasse@2;Hamburg;DE--'
'Main Street@10;Madison;US--'
'Park Boulevard@5A;Hong Kong;HK--'

Let’s take the first row. You want to put “Haupstrasse” into column Street, “2” into column StreetNo, “Hamburg” into column City, and “DE” into column Country.

Pay attention to the string delimiter: an at (@), a semicolon (;), and double dash (–).

First of all, I want to put the whole strings above into a table variable (in a real case, the string might be already in a real table).

DECLARE @T TABLE
(
 customer_address NVARCHAR(100)
)
INSERT INTO @T 
VALUES
    ('Hauptstrasse@2;Hamburg;DE--'),
    ('Main Street@10;Madison;US--'),
    ('Park Boulevard@5A;Hong Kong;HK--')

With CROSS APPLY, you can do it easily:

SELECT 
  substring(customer_address, 1, P1.Pos - 1)                    AS Street,
  substring(customer_address, P1.Pos + 1, P2.Pos - P1.Pos - 1)  AS StreetNo,
  substring(customer_address, P2.Pos + 1, P3.Pos - P2.Pos - 1)  AS City,
  substring(customer_address, P3.Pos + 1, P4.Pos - P3.Pos - 1)  AS Country
FROM @T
  CROSS APPLY (SELECT (charindex('@', customer_address)))               AS P1(Pos)
  CROSS APPLY (SELECT (charindex(';', customer_address, P1.Pos+1)))     AS P2(Pos)
  CROSS APPLY (SELECT (charindex(';', customer_address, P2.Pos+1)))     AS P3(Pos)
  CROSS APPLY (SELECT (charindex('--', customer_address, P3.Pos+1)))    AS P4(Pos)

You can see (and easily download) the source code in my GitHub repo as well.

And here is the result:

---

Photo by Roman Synkevych on Unsplash