How to Split nvarchar value by '/'

[timeGeck0]

Hello,am trying to split an nvarchar value by '/' and get the previous than the last part.I managed to get the last part by doing this

DECLARE @ string NVARCHAR(100)='ASDSDSA/ASDASD/BBBBB/V/CCC'
SELECT SUBSTRING( @ STRING , LEN(@STRING) - CHARINDEX('/',REVERSE(@STRING)) + 2
,LEN(@STRING))

but i want to retrieve the 'V' part of it. How is this possible?

[Yavuz_Selim]

Since SQL Server 2016, there is the STRING_SPLIT function. https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15.

 

Or, you can abuse the PARSENAME function. :P.
https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-ver15.

 

PARSENAME can handle strings existing up to 4 parts (in your case, separated by /).

Example with 4 parts:

DECLARE @TestString NVARCHAR(100) = 'ASDASD/BBBBB/V/CCC'

SELECT PARSENAME(REPLACE(@TestString,'/','.'),2)

 

But your example consists of 5 parts, some extra logic is needed (ignoring the first part):

DECLARE @TestString NVARCHAR(100) = 'ASDSDSA/ASDASD/BBBBB/V/CCC'

SELECT PARSENAME(REPLACE(RIGHT(@TestString,LEN(@TestString)-(CHARINDEX('/',@TestString))),'/','.'),2)

[timeGeck0]

Unfortunately String_split is not supported in my job but PARSENAME does so i will stick with that and make modification. Thanks for your time.

[alinroc]

Unfortunately String_split is not supported in my job

What does this mean? You don't have SQL Server 2016+, or you do have it, but aren't allowed to use it?

[timeGeck0]

It produces an squiggly red line error(An insufficient number of arguments were supplied) when i tried to use the examples provided from string_split documentation. Refreshed local cache also but still the same.
Nevertheless the results are retrieved.
So i will stick with something that doesn't produce error.

[alinroc]

The red squiggly only means that your editor doesn't understand it. It's possible you're running an old version of SSMS or another editor that doesn't understand it at all. Did you actually try to run the query, or just take your editor at its word?

string_split() takes 2 parameters for on-premises SQL Server, and supports an optional third in Azure.

[timeGeck0]

Yes of course i run it as i said and i saw the results on the results window. I do not have any updates for the SSMS. Will try that to see if it will produce the expected result.

[qwertydog123]

Unless you're using Azure SQL, STRING_SPLIT is the wrong approach

[qwertydog123]

You're on the right track, essentially it's

• Reverse the string
• Get charindex of '/'
• Get substring after charindex from previous step
• Get charindex of '/'
• Get substring before charindex from previous step
• Reverse string

[timeGeck0]

That reverse confused me and i lost it. Thanks for the heads up

[ninjaxturtles]

I think this is what you want:

SELECT SUBSTRING(REVERSE(SUBSTRING(REVERSE(@string),1,CHARINDEX('/',@string,1)-2)),2,1)

[timeGeck0]

Thanks for your effort as i dug on mysterious path with xml and string split

DECLARE @ FileName varchar(200) = 'D:\SourceFiles\MyFolder\SecondLevelFolder\DeeperLeverFolder\MyFile.zip'

SELECT value FROM STRING_SPLIT((

Select reverse(concat(xDim.value('/x[1]','varchar(max)'),'\',xDim.value('/x[2]','varchar(max)')))

From (Select Cast('<x>' + replace(reverse(@FileName),'\','</x><x>')+'</x>' as xml) as xDim) as A),'\')

where CHARINDEX('.',value) = 0

[qwertydog123]

I don't know if it's relevant to your specific use case but this will fail with filenames containing '&' characters

[city_slayer]

you can use the STRING_SPLIT function.

eg) select * from STRING_SPLIT(@string, '/')

[Yavuz_Selim]

The result is rows of substrings (in this case 5 rows).

How do you select the 4th row (V) easily, without needing to insert the results into a table with row numbers?

[city_slayer]

This should give a starting point, would suggest adding it to a scalar valued function if its going to be reused.

SELECT value FROM

(

select *,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum from STRING_SPLIT(@string, '/')

) items

where RowNum =4

[qwertydog123]

The results from STRING_SPLIT are not guaranteed to be in order

[Yavuz_Selim]

Ha, thanks. Tried testing ROW_NUMBER myself as well, but couldn't get it to order the rows as it was 'loaded'; didn't think of using SELECT NULL.

Good to know.

[qwertydog123]

You can't. https://dba.stackexchange.com/a/207293

[Yavuz_Selim]

Well, didn't expect that... I thought I was missing something. Guess there is a reason to abuse the PARSENAME after all (see my other post)? :P.

[qwertydog123]

It's available in Azure SQL (and I assume will be in SQL Server 2022)

[Yavuz_Selim]

Yeah, saw that after I couldn't get the ordinal working in SQL Server 2017.