SQL Synaxt to find the string between '/'

Transact-SQL
1

Hi ALL I am looking the azure sql synatax whic help me to gte the values betwen the string

create table #test
(
ID varchar(2000)
)

insert into #test
SELECT '30002 / 40010 /90000 / /20000/30000/' UNION ALL
SELECT '30002 /' UNION ALL
SELECT '30002 / 40010 /' UNION ALL
SELECT '30002 / 40010 / /' UNION ALL
SELECT '30002 / 40010 / / 87900 / 54678/ '

image

2

hi

hope this helps

i was able to get the output

but i have to make changes to your input data format
. i had to make it uniform and consistent
i also had to add an identity column for row identification purpose

image

; with cte as 
(
SELECT 
 ROW_NUMBER() over( partition by rn123 order by id ) as rn ,    rn123,value 
FROM #test 
cross apply 
string_split(ID,'/')
) , cte_2 as 
(
select string_agg(cast(rn as varchar) +'*'+value,'/')as ID from cte group by rn123
)
select 
  SUBSTRING(ID, charindex('1*',ID)+2,charindex('2*',ID) - charindex('1*',ID)-3) ,
  SUBSTRING(ID, charindex('2*',ID)+2,charindex('3*',ID) - charindex('2*',ID)-3) ,
  SUBSTRING(ID, charindex('3*',ID)+2,charindex('4*',ID) - charindex('3*',ID)-3) ,
  SUBSTRING(ID, charindex('4*',ID)+2,charindex('5*',ID) - charindex('4*',ID)-3) ,
  SUBSTRING(ID, charindex('5*',ID)+2,charindex('6*',ID) - charindex('5*',ID)-3) ,
  SUBSTRING(ID, charindex('6*',ID)+2,charindex('7*',ID) - charindex('6*',ID)-3) 
from cte_2

image
image1353×251 6.42 KB

3

I'm using the publicly available function "dbo.DelimitedSplit8K". You can Google it if you don't already have it. I made no changes to the raw data.


SELECT ca1.*
FROM #test
CROSS APPLY (
    SELECT 
        MAX(CASE WHEN ItemNumber = 1 THEN LTRIM(RTRIM(Item)) ELSE '' END) AS Val1,
        MAX(CASE WHEN ItemNumber = 2 THEN LTRIM(RTRIM(Item)) ELSE '' END) AS Val2,
        MAX(CASE WHEN ItemNumber = 3 THEN LTRIM(RTRIM(Item)) ELSE '' END) AS Val3,
        MAX(CASE WHEN ItemNumber = 4 THEN LTRIM(RTRIM(Item)) ELSE '' END) AS Val4,
        MAX(CASE WHEN ItemNumber = 5 THEN LTRIM(RTRIM(Item)) ELSE '' END) AS Val5,
        MAX(CASE WHEN ItemNumber = 6 THEN LTRIM(RTRIM(Item)) ELSE '' END) AS Val6
    FROM (
        SELECT ItemNumber, Item
        FROM dbo.DelimitedSplit8K(ID, '/')
    ) AS ds
) AS ca1
4

you can make use of SwePeso's fnParseString() from here.

select *, dbo.fnParseString(-1, '/', ID)
        , dbo.fnParseString(-2, '/', ID)
        , dbo.fnParseString(-3, '/', ID)  
        , dbo.fnParseString(-4, '/', ID)  
        , dbo.fnParseString(-5, '/', ID)  
        , dbo.fnParseString(-6, '/', ID)    
from   #test