SQLTeam.com | Weblogs | Forums

String Parsing in Query



I have CSV data in a column delimited by a colon:

[{"CreatedDate":"17.08.2015 08:56:55","CreatedBy":"t131987","Message":"CES, Manual Check, AB","LinkTitle":null,"LinkURL":null},{"CreatedDate":"14.08.2015 09:10:38","CreatedBy":"t131987","Message":"CES, Manual Check, KSP","LinkTitle":null,"LinkURL":null}]

If I wanted to query this column, but only return components of the string, how can I manipulate it? In other words, If in the above, I only wanted to get the Messages, how can I do that?

So for the above, I would want to return "CES, Manual Check, AB" and "CES, Manual Check, KSP"

I'm thinking a stored procedure would have to be used to loop through the values, find each occurance of "Message", read the subsequent data, and catalog them. I do not know if a simple Select statement with string functions can pull this out.

This is on SQL 2008 FYI.

Thanks for any help.


Those colons within the date-time data are going to make this a bit convoluted. Having said that...`

declare @tbl table (
csvdata varchar(400)

insert into @tbl(csvdata)
('{"CreatedDate":"17.08.2015 08:56:55","CreatedBy":"t131987","Message":"CES, Manual Check, AB","LinkTitle":null,"LinkURL":null}'),
('{"CreatedDate":"14.08.2015 09:10:38","CreatedBy":"t131987","Message":"CES, Manual Check, KSP","LinkTitle":null,"LinkURL":null}')

--/**/select * from @tbl

substring(s.Element, charindex('":"', s.Element) + 3, 100)
@tbl t
outer apply
dbo.SplitIt(csvdata, '","') s
-- dbo.parseCSV(csvdata, '","')
s.ElementId = 3
CREATE FUNCTION [dbo].[SplitIt] (
@vcDelimitedString varchar(8000),
@vcDelimiter varchar(100)
** ScriptName: SplitIt
** Description: Accepts a delimited string and splits it at the specified
** delimiter points. Returns the individual items as a table data
** type with the ElementID field as the array index and the Element
** field as the data
** @vcDelimitedString - The string to be SplitIt
** @vcDelimiter - String containing the delimiter where
** delimited string should be SplitIt
** Returns: Table data type containing array of strings that were SplitIt with
** the delimiters removed from the source string
** sample call:
** SELECT ElementID, Element FROM SplitIt('11111,22222,3333', ',') ORDER BY ElementID

ElementID smallint IDENTITY(1,1), --Array index
Element varchar(1000) --Array element contents
DECLARE @siIndex smallint
DECLARE @siStart smallint
DECLARE @siDelSize smallint

SELECT @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
	SELECT @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
	IF @siIndex = 0
		INSERT INTO @tblArray VALUES(@vcDelimitedString)
		INSERT INTO @tblArray 
		VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
		SELECT @siStart = @siIndex + @siDelSize
		SELECT @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)


It's not the fastest string splitter in the world but it does return an index number for each element which I've used to select the Nth element.


Maybe something like below. That code uses a "standard tally table", i.e., a table of sequential numbers from 0 to, say, 10,000 (for this task -- typically you load a million rows into a tally table so it can be used for virtually any task).

declare @tbl table (
id int,
csvdata	varchar(400)
insert into @tbl(id, csvdata)
'{"CreatedDate":"17.08.2015 08:56:55","CreatedBy":"t131987","Message":"CES, Manual Check, AB","LinkTitle":null,"LinkURL":null},{"CreatedDate":"14.08.2015 09:10:38","CreatedBy":"t131987","Message":"CES, Manual Check, KSP","LinkTitle":null,"LinkURL":null}')
    SELECT id, message
    FROM @tbl
        SELECT SUBSTRING(csvdata, tally + 10, CHARINDEX('"', csvdata, tally + 11) - (tally + 10) + 1) AS message
        FROM dbo.tally
        WHERE SUBSTRING(csvdata, tally, 10) = '"Message":'
    ) AS ca1


Both you guys rock, thank you.....works great.


Can the message "string" contain an embedded double-quote? (e.g. doubled up, or escaped with (") or similar? If so that will spoil your day :frowning: