tsql, picking out value-pairs

Question

I have a column that has the following data:

PersonId="315618" LetterId="43" MailingGroupId="1" EntityId="551723" trackedObjectId="9538" EmailAddress="[email protected]"

Is there any good, clean tsql syntax to grab the 551723 (the value associated with EntityId). The combination of Substring and Patindex I'm using seems quite unwieldy.

Answer 1

If that's the text in the column, then you're going to have to use substring at some stage.

 declare @l_debug varchar(1000)
select @l_debug = 'PersonId="315618" LetterId="43" MailingGroupId="1" EntityId="551723" trackedObjectId="9538" EmailAddress="[email protected]"'

select  substring(@l_debug, patindex('%EntityId="%', @l_debug)+ 10, 6)

If you don't know how long EntityID could be, then you'll need to get the patindex of the next double-quote after EntityID="

declare @l_debug varchar(1000), @l_sub varchar(100), @l_index2 numeric
select @l_debug = 'PersonId="315618" LetterId="43" MailingGroupId="1" EntityId="551723" trackedObjectId="9538" EmailAddress="[email protected]"'

select @l_sub = substring(@l_debug, patindex('%EntityId="%', @l_debug)+ 10 /*length of "entityid=""*/, char_length(@l_debug))


select  @l_index2 =  patindex('%"%', @l_sub)

select substring(@l_debug, patindex('%EntityId="%', @l_debug)+ 10, @l_index2 -1)

Answer 2

If you possibly can, break out your data. Either normalize your tables or store XML in the column (with an XML data type) instead of name, value pairs. You'll then be able to use the full power and speed of SQL Server, or at least be able to issue XPath queries (assuming a relatively recent version of SQL Server).

I know this probably won't help you in the short term, but it's a goal to work towards. :)

Answer 3

Substring(

Substring(EventArguments,PATINDEX('%EntityId%', EventArguments)+10,10),0, 
PATINDEX('%"%', Substring(EventArguments,
PATINDEX('%EntityId%', EventArguments)+10,10))

)

Answer 4

That strings looks just like an XML attribute list for an element, so you can wrap it into an XML element and use xpath:

declare @t table (t nvarchar(max));
insert into @t (t) values (
    N'PersonId="315618" LetterId="43" MailingGroupId="1" 
     EntityId="551723" trackedObjectId="9538" 
     EmailAddress="[email protected]"');


with xte as (
    select cast(N'<x '+t+N'/>' as xml) as x from @t)
select 
    n.value(N'@PersonId', N'int') as PersonId
    , n.value(N'@LetterId', N'int') as LetterId
    , n.value(N'@EntityId', N'int') as EntityId 
    , n.value(N'@EmailAddress', N'varchar(256)') as EmailAddress
    from xte
    cross apply x.nodes(N'/x') t(n);

Whether this is better or worse that string manipulation depends on a variety of factors, not least the size of the string and number of records to parse. I preffer the simple and clean xpath syntax over char index based manipulation (the code is much more maintainable).