Hi - Wondering if someone might help with this. I've got some data in Access and using the SQL view to write my query.
I've got a column with data parsed by semicolons. I am trying to extract out any string between semicolons that meet certain criteria. There is no pattern to the data at all (it is a bunch of paths that vary).
Example Data of the column
Row1: path1\dude1;path2\dude2;path3\dude3;path4\dude4
Row2: path2\dude2;path4\dude4
Row3: path1\dude1;path3\dude3
I'm looking to pull the full string between the semicolons for dude2. The problem is dude2 could be anywhere in the column.
One option is to SPLIT the column, on the semi-colon delimiter, into one-row-per-element, stores those in a Child Table associated with the Parent Table (it can be a #Temporary Table if you don't need it to be persistent), and then JOIN the Child Table to the Parent Table for your "Find the DUDE2 ones"
Another option is to search for rows that contain "dude2;" and then locate the start/end of that portion of the string. This is quite messy to do in SQL I'm afraid.
WITH [Example Data of the column cte]
AS
(
SELECT Id='Row1', Path1= 'path1\dude1;path2\dude2;path3\dude3;path4\dude4'
UNION SELECT 'Row2', 'path2\dude2;path4\dude4'
UNION SELECT 'Row3', 'path1\dude1;path3\dude3'
)
SELECT Id, Substring(Path1, sv.number,
CharIndex( '\'
, Path1 + '\'
, sv.number + 1
) - sv.number
) SplitPath1
FROM
master.dbo.spt_values sv
CROSS JOIN
[Example Data of the column cte] t
WHERE
sv.type='P'
AND
number BETWEEN 1 AND Len(Path1)
AND
Substring( '\' + Path1, sv.number, 1)= '\';
I was thinking more of splitting on ";" then then finding the rows that contained "dude2" and then "doing something with them" - e.g. processing their PATH
So using your very helpful code
WITH [Example Data of the column cte]
AS
(
SELECT Id='Row1', Path1= 'path1\dude1;path2\dude2;path3\dude3;path4\dude4' + ';'
UNION SELECT 'Row2', 'path2\dude2;path4\dude4' + ';'
UNION SELECT 'Row3', 'path1\dude1;path3\dude3' + ';'
)
SELECT Id, Substring(Path1, sv.number,
CharIndex( ';'
, Path1 + ';'
, sv.number + 1
) - sv.number
) SplitPath1
FROM
master.dbo.spt_values sv
CROSS JOIN
[Example Data of the column cte] t
WHERE
sv.type='P'
AND
number BETWEEN 1 AND Len(Path1)
AND
Substring( ';' + Path1, sv.number, 1)= ';';
