String Parsing in Query

Transact-SQL
1

Hello,

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.

2

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)
values
('{"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

select
substring(s.Element, charindex('":"', s.Element) + 3, 100)
from
@tbl t
outer apply
dbo.SplitIt(csvdata, '","') s
-- dbo.parseCSV(csvdata, '","')
where
s.ElementId = 3
go
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
**
*/

RETURNS @tblArray TABLE
(
ElementID smallint IDENTITY(1,1), --Array index
Element varchar(1000) --Array element contents
)
AS
BEGIN
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
BEGIN
	SELECT @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
	IF @siIndex = 0
	BEGIN
		INSERT INTO @tblArray VALUES(@vcDelimitedString)
		BREAK
	END
	ELSE
	BEGIN
		INSERT INTO @tblArray 
		VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
		SELECT @siStart = @siIndex + @siDelSize
		SELECT @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
	END
END

RETURN

END
`
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.

1 Like
3

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)
values(1,
'{"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
    CROSS APPLY (
        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
1 Like
4

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

5

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: