SQLTeam.com | Weblogs | Forums

Parsing Data Between Delimiters

access

#1

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.

Thanks!


#2

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.


#3
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)= '\';


#4

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 :slight_smile:

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)= ';';

gives me this

Id   SplitPath1                                       
---- ------------------------------------------------ 
Row1 path1\dude1
Row1 path2\dude2
Row1 path3\dude3
Row1 path4\dude4
Row2 path2\dude2
Row2 path4\dude4
Row3 path1\dude1
Row3 path3\dude3

#5

Splitting strings is easy in SQL Server... Not sure how the solutions are going to fare in MS Access...


#6

Thanks, I missed that.


#7

I was about 1/2 way through a solution before I noticed it myself...


#8

Bit late now! but I've added an [access] TAG