How to substring records with variable length

Question

I have a table which has a column with doc locations, such as AA/BB/CC/EE

I am trying to get only one of these parts, lets say just the CC part (which has variable length). Until now I've tried as follows:

SELECT RIGHT(doclocation,CHARINDEX('/',REVERSE(doclocation),0)-1) 
FROM Table 
WHERE doclocation LIKE '%CC %'

But I'm not getting the expected result

Answer 1

Use PARSENAME function like this,

DECLARE @s VARCHAR(100) = 'AA/BB/CC/EE'

SELECT PARSENAME(replace(@s, '/', '.'), 2)

Answer 2

The PARSENAME function is used to get the specified part of an object name, and should not used for this purpose, as it will only parse strings with max 4 objects (see SQL Server PARSENAME documentation at MSDN) SQL Server 2016 has a new function STRING_SPLIT, but if you don't use SQL Server 2016 you have to fallback on the solutions described here: How do I split a string so I can access item x?

Answer 3

This is painful to do in SQL Server. One method is a series of string operations. I find this simplest using outer apply (unless I need subqueries for a different reason):

select *
from t outer apply
     (select stuff(t.doclocation, 1, patindex('%/%/%', t.doclocation), '') as doclocation2) t2 outer apply
     (select left(tt.doclocation2), charindex('/', tt.doclocation2) as cc
     ) t3;

Answer 4

The question is not clear I guess. Can you please specify which value you need? If you need the values after CC, then you can do the CHARINDEX on "CC". Also the query does not seem correct as the string you provided is "AA/BB/CC/EE" which does not have a space between it, but in the query you are searching for space WHERE doclocation LIKE '%CC %'

SELECT SUBSTRING(doclocation,CHARINDEX('CC',doclocation)+2,LEN(doclocation)) 
FROM Table 
WHERE doclocation LIKE '%CC %'