Extract data between two slashes

Question

I'm struggling to get my head around a problem I have, I'm trying to create two fields from one Productcategories.fullpath.

The first will take the data before the first slash, which is working well.

Below is the code I have for this:

LEFT(Productcategories.Fullpath, CHARINDEX('/',ProductCategories.Fullpath + '/') - 1) as shortpath,

The second issue I have, is I now need to also extract between the second and third slash into another column called middlepath.

I can't get this to work for love nor money, the data is made up of three units separated by /.

If anyone can help, I will be eternally grateful, as SQL I can do a little, but i'm no programmer.

Data looks like Products\lifts\small lifts (length can vary considerably).

Output should be the "Lifts" part

Tag appropriate database name. Add sample data and expected output also. — mkRabbani, Jun 27 '19 at 10:17
Is this SQL Server? Can you use STRING_SPLIT? There are a few questions here about processing comma-separated lists in SQL so maybe you can adapt one of those. — Rup, Jun 27 '19 at 10:17
Different database products use different dialects of SQL. string manipulation code is usually product specific. Please add the tag for the database product you are using (SQL Server, Oracle, MySql, PostgreSql etc'). A specific version tag is also recommended, since different versions have different capabilities. — Zohar Peled, Jun 27 '19 at 10:24
you create split function ,and use many time more info :https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql — Mangesh Auti, Jun 27 '19 at 10:28
Use RIGHT to get everything after your first '/', then wrap that in your LEFT statement. — OTTA, Jun 27 '19 at 10:30

Red Devil · Answer 1 · 2019-06-27T10:55:17.297

0

This will give you the Output as lifts.

declare @t table (Files varchar(50))

insert into @t values ('Products\lifts\small')

select substring(files,CHARINDEX('\',files)+1,CHARINDEX('\', files, (CHARINDEX('\', files)+1)) - CHARINDEX('\',files)-1) from @t

edited Jun 27 '19 at 10:55

answered Jun 27 '19 at 10:26

Red Devil

2,343
2
21
41

Thank you for the reply, should i be using it like this – Nick Palmer Jun 27 '19 at 10:27
declare @t table (Files varchar(50)) insert into @t values (productcategories.fullpath) select replace(substring(files,CHARINDEX('\',files)+1,len(files)),'\','') from @t – Nick Palmer Jun 27 '19 at 10:28

forpas · Accepted Answer · 2019-06-27T13:30:42.687

0

You can use 3 string functions: left(), right() and charindex() like this:

select
  left(Fullpath, charindex('\', Fullpath) - 1) shortpath,
  left(
    right(Fullpath, len(Fullpath) - charindex('\', Fullpath)),
    charindex('\', right(Fullpath, len(Fullpath) - charindex('\', Fullpath))) - 1
  ) middle
from Productcategories

See the demo.
Result:

> shortpath | middle
> :-------- | :-----
> Products  | lifts

For the case that there is only 1 "\" or none it's more complicated:

select
  case charindex('\', Fullpath)
     when 0 then Fullpath
     when 1 then ''
     else left(Fullpath, charindex('\', Fullpath) - 1)                            
  end shortpath,                                
  case charindex('\', Fullpath)
     when 0 then ''
     else case charindex('\', Fullpath, charindex('\', Fullpath) + 1)
       when 0 then substring(Fullpath, charindex('\', Fullpath) + 1, len(Fullpath))
       else left(
         right(Fullpath, len(Fullpath) - charindex('\', Fullpath)),
         charindex('\', right(Fullpath, len(Fullpath) - charindex('\', Fullpath))) - 1
       )         
     end            
  end middle                                
from Productcategories

See the demo.

edited Jun 27 '19 at 13:30

answered Jun 27 '19 at 11:01

forpas

160,666
10
38
76

Hi, thank you for the reply. I seem to be getting "Invalid length parameter passed to the LEFT or SUBSTRING function when i run with this. can you advise at all? thank you – Nick Palmer Jun 27 '19 at 13:02
Is there a case that in the string there is only 1 "\" ? Or none? – forpas Jun 27 '19 at 13:05
Im really sorry but i dont follow, if referring to blank space, there can be many – Nick Palmer Jun 27 '19 at 13:06
Ah ok i see now, there will always be at least 2 – Nick Palmer Jun 27 '19 at 13:06
If there are at least 2 you can see from the demo that this code is working. Is there a case that instead of "\" there is "//", or that there are 2 consecutive "\\"? – forpas Jun 27 '19 at 13:08
Sorry, just double checked the database, there are fields with 1 and some with none, they are never consecutive – Nick Palmer Jun 27 '19 at 13:08
Thank you so much for this, i must be doing something wrong though, as Shortpath now mirrors fullpath, and middle has nothing in it at all. I will double check all i have here. thank you again – Nick Palmer Jun 27 '19 at 13:40
*Shortpath now mirrors fullpath, and middle has nothing in it at all* Check the 2nd demo. When there is no "\" then Shortpath = fullpath and middle = ''. – forpas Jun 27 '19 at 13:43
I agree, the demo works fine, im checking the rest of the query my end to see whats affecting it. thank you for all your help. – Nick Palmer Jun 27 '19 at 13:55

Extract data between two slashes

2 Answers2