'Multi-character delimiter with STRING_SPLIT

I want to split values based on multiple values like ! and %, operators, but STRING_SPLIT only allows 1-character delimiters.

My string is like this:

99001316 - ABCD 250 ML!%!%99001234 - CDEF 500 ML!%!%99001995 - OWEW 50 ML

And what I want is to separate the numeric and text value. When I do this:

SELECT value FROM STRING_SPLIT(@x, '%',1)

I'm getting

value
99001234 - ABCD 250 ML!
!
99001230 - CDEF 500 ML!
!
99001995 - OWEW 50 ML

And what I want is

value string
99001234 ABCD 250 ML!
99001230 CDEF 500 ML!
99001995 OWEW 50 ML
sql-servertsqlsplitdelimiter


Solution 1:[1]

One option is to parse the string via JSON. You may notice the delimiter of '!%!%'

Declare @X varchar(max) = '99001316 - ABCD 250 ML!%!%99001230 - CDEF 500 ML!%!%99001395 - OWEW 50 ML'


Select Value  = rtrim(left(value,charindex('-',Value+'-')-1))
      ,String = ltrim(substring(value,charindex('-',Value+'-')+1,len(Value)))
 From  OpenJSON( '["'+replace(string_escape(@X,'json'),'!%!%','","')+'"]' )

Resutls

Value       String
99001316    ABCD 250 ML
99001230    CDEF 500 ML
99001395    OWEW 50 ML

Solution 2:[2]

Another way to do it.

SQL

DECLARE @tokens NVARCHAR(max) = N'99001316 - ABCD 250 ML!%!%99001234 - CDEF 500 ML!%!%99001995 - OWEW 50 ML';

WITH rs AS
(
    SELECT value AS token 
    FROM STRING_SPLIT(REPLACE(@tokens,'!%!%','%') , '%')
)
SELECT * 
, LEFT(token, pos - 1) AS [value]
, RIGHT(token, LEN(token) - pos - 1) AS [string]
FROM rs
    CROSS APPLY (SELECT CHARINDEX('-', token)) AS t(pos);

Output

+------------------------+-----+-----------+-------------+
|         token          | pos |   value   |   string    |
+------------------------+-----+-----------+-------------+
| 99001316 - ABCD 250 ML |  10 | 99001316  | ABCD 250 ML |
| 99001234 - CDEF 500 ML |  10 | 99001234  | CDEF 500 ML |
| 99001995 - OWEW 50 ML  |  10 | 99001995  | OWEW 50 ML  |
+------------------------+-----+-----------+-------------+

Solution 3:[3]

Here are some more ideas

DECLARE @X VARCHAR(MAX) = '99001316 - ABCD 250 ML!%!%99001230 - CDEF 500 ML!%!%99001395 - OWEW 50 ML';

WITH s1 AS  (
            SELECT      ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ord1
                        , s1.value
            FROM        STRING_SPLIT(REPLACE(@X, '!%!%', '|'), '|') s1
            ),
s2 AS       (
            SELECT      s1.ord1
                        , ROW_NUMBER() OVER (PARTITION BY s1.ord1 ORDER BY (SELECT 1)) AS ord2
                        , s2.value
            FROM        s1
            CROSS APPLY STRING_SPLIT(REPLACE(s1.value, ' - ', '|'), '|') s2
            )
SELECT      MAX(CASE WHEN s2.ord2 = 1 THEN s2.value END) AS id
            , MAX(CASE WHEN s2.ord2 = 2 THEN s2.value END) AS text
FROM        s2
GROUP BY    s2.ord1

You can include ordinal column with STRING_SPLIT on Azure (passing 1 in 3-rd parameter) and remove the ROW_NUMBER usage for both ord columns above.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 miriamka
Solution 3 wqw

Related Questions