Split string by space and '-' SQL Server

Question

I have the following column value:

test-00 - test-01 - test-02

I need to get the string between ' -' and ' -'

test-01

Is it possible in SQL Server?

I've tried with :

STRING_SPLIT(column, ' -')

but it works only for '-' not for ' -'

score 3 · Answer 1 · answered Jul 29 '21 at 15:21

3

You could use replace() to make a single character delimiter:

string_split(replace(column, ' - ', '|'), '|')

I am guessing you understand the rest of the logic because you mention splitting the string this way in your question.

answered Jul 29 '21 at 15:21

Gordon Linoff

1,242,037
58
646
786

I have an additional question, is it possible to select only the second part od that string? – inspiredd Jul 29 '21 at 15:37
@inspiredd . . . Yes, but that did not appear to be your question. – Gordon Linoff Jul 29 '21 at 18:19

score 2 · Accepted Answer · answered Jul 29 '21 at 15:49

Please try the following solution.

SQL

-- DDL and data population, start
DECLARE @tbl Table  (ID INT IDENTITY PRIMARY KEY, Tokens VARCHAR(MAX));
INSERT INTO @tbl (Tokens) VALUES
('test-00 - test-01 - test-02'),
('test-03 - test-04 - test-05');
-- DDL and data population, end

SELECT ID, Tokens
    , PARSENAME(c, 2) as [token-in-between]
FROM @tbl
    CROSS APPLY (VALUES (REPLACE(Tokens, ' - ','.'))) AS t(c);

Output

+----+-----------------------------+------------------+
| ID |           Tokens            | token-in-between |
+----+-----------------------------+------------------+
|  1 | test-00 - test-01 - test-02 | test-01          |
|  2 | test-03 - test-04 - test-05 | test-04          |
+----+-----------------------------+------------------+

score 0 · Answer 3 · edited Aug 02 '21 at 20:52

You can try the following:

WITH cte AS(
  SELECT 'test-00 - test-01 - test-02' AS x
  UNION ALL
  SELECT 'test-03 - test-04 - test-05'
),
cteRn AS(
SELECT *, ROW_NUMBER() OVER (ORDER BY x) rn
  FROM cte
),
cteSplit AS(
SELECT rn, ROW_NUMBER() OVER(PARTITION BY rn ORDER BY value) rnFilter, TRIM(value) AS value
  FROM cteRn
  OUTER APPLY STRING_SPLIT(REPLACE(REPLACE(x, ' -', '|'), '- ', '|'), '|')
)
SELECT value
  FROM cteSplit
  WHERE rnFilter = 2

However, this will only work as long as you really want to split by '- ' and ' -'.

score 0 · Answer 4 · answered Jul 29 '21 at 15:36

0

You can use the below query:

SELECT REGEXP_SUBSTR(str, '[ - ]+', 1, NULL) AS substr
FROM (
    SELECT COLUMN_NAME AS str FROM DUAL
)

Refer to this question https://stackoverflow.com/a/17597049/2429434

answered Jul 29 '21 at 15:36

Mhd Wael Jazmati

636
9
18

score 0 · Answer 5 · edited Aug 02 '21 at 20:51

Here is another method

 --==== Some test data
Declare @testData Table (TestData varchar(100));
Insert Into @testData (TestData)
Values ('test-00 - test-01 - test-02')
     , ('test-03 - test-04 - test-05');

 Select col1 = substring(v.TestData, 1, p1.pos - 2)
      , col2 = substring(v.TestData, p1.pos, p2.pos - p1.pos - 1)
      , col3 = substring(v.TestData, p2.pos, p3.pos - p2.pos - 1)
   From @testData                                                               As td
  Cross Apply (Values (concat(replace(td.TestData, ' - ', '|'), '|||')))        As v(TestData)
  Cross Apply (Values (charindex('|', v.TestData, 1) + 1))                      As p1(pos)
  Cross Apply (Values (charindex('|', v.TestData, p1.pos) + 1))                 As p2(pos)
  Cross Apply (Values (charindex('|', v.TestData, p2.pos) + 1))                 As p3(pos)

Split string by space and '-' SQL Server

5 Answers5