SQL: how to select a substring between special characters

Question

My string looks something like this:

\\\abcde\fghijl\akjfljadf\\
\\xyz\123

I want to select everything between the 1st set and next set of slashes

Desired result:

abcde
xyz

EDITED: To clarify, the special character is always slashes - but the leading characters are not constant, sometimes there are 3 slashes and other times there are only 2 slashes, followed by texts, and then followed by 1 or more slashes, some more texts, 1 or more slash, so on and so forth. I'm not using any adapter at all, just looking for a way to select this substring in my SQL query

Please advise.

Thanks in advance.

Solution 1

Use substring, like this (only works for the specified pattern of two slashes, characters, then another slash):

declare @str varchar(100) = '\\abcde\cc\xxx'

select substring(@str, 3, charindex('\', @str, 3) - 3)

Replace @str with the column you actually want to search, of course.

The charindex returns the location of the first slash, starting from the 3rd character (i.e. skipping the first two slashes). Then the substring returns the part of your string starting from the 3rd character (again, skipping the first two slashes), and continuing until just before the next slash, as determined by charindex.

Edit: To make this work with different numbers of slashes at the beginning, use patindex with regex to find the first alphanumeric character, instead of hardcoding that it should be the third character. Example:

declare @str varchar(100) = '\\\1Abcde\cc\xxx'

select substring(@str, patindex('%[a-zA-Z0-9]%', @str), charindex('\', @str, patindex('%[a-zA-Z0-9]%', @str)) - patindex('%[a-zA-Z0-9]%', @str))

Solution 2

You could do a cross join to find the second position of the backslash. And then, use substring function to get the string between 2nd and 3rd backslash of the text like this:

SELECT substring(string, 3, (P2.Pos - 2)) AS new_string
FROM strings
CROSS APPLY (
    SELECT (charindex('\', replace(string, '\\', '\')))
    ) AS P1(Pos)
CROSS APPLY (
    SELECT (charindex('\', replace(string, '\\', '\'), P1.Pos + 1))
    ) AS P2(Pos)

SQL Fiddle Demo

UPDATE

In case, when you have unknown number of backslashes in your string, you could just do something like this:

DECLARE @string VARCHAR(255) = '\\\abcde\fghijl\akjfljadf\\'

SELECT left(ltrim(replace(@string, '\', ' ')), 
       charindex(' ',ltrim(replace(@string, '\', ' ')))-1) AS new_string

SQL Fiddle Demo2

Comments

[Gia]

Thank you! This works, but is there a way to it to extract the text between the first set of slashes despite how many slashes it begins with? For instance, what if sometimes there are 3 leading slashes and sometimes there are just 2

[Gia]

thanks, but for some reason it returned error: incorrect syntax

[Gia]

thank you, but what if the leading number of blackslashes are not constant? sometimes there are 2 and sometimes 3

[FutbolFan]

@Gia In that case, I suppose you would need to find the position of the first alphabetical characters between your slashes.

[APH]

@Gia I have edited my answer and added an example for looking for the first alphanumeric character, to allow for no slashes or many slashes at the beginning of the string.

[Zoldren]

It works when I copy and paste it from here into my prompt...Are you getting the last ) at the end on its own line?

[Gia]

my bad, it does work if there are 2 leading backslashes, but it won't return the results when there are 3 leading backslashes instead of 2, is there a workaround?

[APH]

@Gia why did you unaccept? Did you discover a problem with the answer?

[FutbolFan]

@Gia Sorry, I added -1 to the left function so, it doesn't pick up any trailing spaces. Please add that to your query.

[Gia]

opps! sorry i must have clicked twice by mistake, too excited that it worked lol. My apologies!

[Gia]

thank you again! Sorry i'm new here, am I only allowed to accept one answer??

[FutbolFan]

@Gia Yeah, no worries! I think you could choose either answer, as both solution works! I think the idea behind choosing the accepted answer is that it works best in the interest of the OP (in this case you). My opinion is that if another answer is useful and works just as great, then an upvote to that solution is usually much appreciated. :o)

[Gia]

Ah i gotcha, i'll definitely vote this up once i've accumulated enough reputation points. thx again