Parse multiple filters in SQL

Question

I have a problem parsing a stored procedure parameter in the form:

declare @S varchar(100)
set @S = '4=2,24=1534'

Here's the query:

    select 
        cast(idx as varchar(100)) 'idx'
    ,   value 
    ,   SUBSTRING(value, 1, charindex(value, '=')+1)  'first'
    ,   SUBSTRING(value, charindex(value, '=')+1, LEN(value)-charindex(value, '=')-1) 'second'
    from Common.SplitToTable(@S, ',') -- returns (idx int, value varchar(max))
    where len(value) > 0

But here is the result I get:

idx    value       first    second
0      4=2         4        4=
1      24=1534     2        24=153

Here's what I expected:

idx    value       first    second
0      4=2         4        2
1      24=1534     2        1534

Help?

Answer 1

The parameters to charindex are backwards (the string you are looking for comes first) and adjust the lengths accordingly:

select 
    cast(idx as varchar(100)) 'idx'
    ,   value 
    ,   SUBSTRING(value, 1, charindex('=', value)-1)  'first'
    ,   SUBSTRING(value, charindex('=', value)+1, LEN(value)-charindex('=',value)) 'second'
    from Common.SplitToTable(@S, ',') -- returns (idx int, value varchar(max))
    where len(value) > 0