-1

Hi I want to cut a string in sql at 2 points with the "/" as an indicator where to cut. The string looks like this: "test1/test2/test3/test4/test5/test6" and I need the parts of test 2 and test 5 but they are not static, so I have to use the "/" befor and after them to set the points where to cut, any suggestions?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Johannes Hamann
  • 11
  • 3
  • 3
    In SQL Server 2016, you can use the built-in `split_string()`. For earlier versions, you can search for such a function on the web. – Gordon Linoff Sep 29 '17 at 11:34
  • Possible duplicate of [How do I split a string so I can access item x?](https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) – MatSnow Sep 29 '17 at 11:57

3 Answers3

2

You could achieve this by using XML:

DECLARE @x NVARCHAR(100) = 'test1/test2/test3/test4/test5/test6';
DECLARE @xml XML = cast(('<X>'+replace(@x,'/' ,'</X><X>')+'</X>') as xml);

WITH cte AS(
  SELECT N.value('.', 'varchar(10)') as value, ROW_NUMBER() OVER (ORDER BY (SELECT(0))) AS rn
    FROM @xml.nodes('X') as T(N)
)
SELECT *
  FROM cte
  WHERE rn IN (1, 5)
Tyron78
  • 4,117
  • 2
  • 17
  • 32
0

If the items that you want are always the second and the second to last you can use CHARINDEX to find the first and second '/' and SUBSTRING to pull that value. Then REVERSE the string to get the second last. e.g.

DECLARE @data varchar(128) = 'test1/test2/test3/test4/test5/test6';

SELECT substring(@data,charindex('/',@data,1)+1,charindex('/',@data,charindex('/',@data,1)+1)-(charindex('/',@data,1)+1)),
       reverse(substring(reverse(@data),charindex('/',reverse(@data),1)+1,charindex('/',reverse(@data),charindex('/',reverse(@data),1)+1)-(charindex('/',reverse(@data),1)+1)))
SQLBadPanda
  • 625
  • 5
  • 7
0

You can use the function CHARINDEX() For example this query will work for you

declare @text varchar(100)='test1/test2/test3/test4/test5/test6'

select 
    SUBSTRING (@text,CHARINDEX('/',@text)+1,CHARINDEX('/',@text,(CHARINDEX('/',@text)+1))-CHARINDEX('/',@text)-1),
    SUBSTRING (@text,CHARINDEX('/',@text,CHARINDEX('/',@text,CHARINDEX('/',@text,CHARINDEX('/',@text)+1)+1)+1)+1,CHARINDEX('/',@text,(CHARINDEX('/',@text)+1))-CHARINDEX('/',@text)-1)

Just incase you want to substring something else, I'm sharing the code below and then you can work on it

declare @text varchar(100)='test1/test2/test3/test4/test5/test6'

select 
SUBSTRING (@text,CHARINDEX('/',@text)+1,CHARINDEX('/',@text,(CHARINDEX('/',@text)+1))-CHARINDEX('/',@text)-1)  --test2
,SUBSTRING (@text,CHARINDEX('/',@text,CHARINDEX('/',@text)+1)+1,CHARINDEX('/',@text,(CHARINDEX('/',@text)+1))-CHARINDEX('/',@text)-1)  --test3
,SUBSTRING (@text,CHARINDEX('/',@text,CHARINDEX('/',@text,CHARINDEX('/',@text)+1)+1)+1,CHARINDEX('/',@text,(CHARINDEX('/',@text)+1))-CHARINDEX('/',@text)-1)  --test4
,SUBSTRING (@text,CHARINDEX('/',@text,CHARINDEX('/',@text,CHARINDEX('/',@text,CHARINDEX('/',@text)+1)+1)+1)+1,CHARINDEX('/',@text,(CHARINDEX('/',@text)+1))-CHARINDEX('/',@text)-1) --test5
Hakan Hifzioglu
  • 35
  • 5