Spliting a column that is a variable/value pair, and assigning a var in sql

Question

I have a query like:

SELECT ...
FROM ...
WHERE .. AND SomeID = @someID

I'm inheriting this massive stored procedure that has some dynamic elements to it, so I need to get the value of someID from a column in another table i.e. perform a lookup.

How can I do that?

The other column looks like:

TypeValue
'SomeID=1232,OtherID=3383'

Some the column TypeValue is VARCHAR(1000), and it contains a comma seperated string of type & value pairs.

So I need to split on the , and then split on the = so somehow get:

SET @SomeID = SELECT TypeValue FROM MyTABLE WHERE ..

How can I do this?

(note, this is sql server 2000)

Can you restructure your data model to avoid that encoding in the first place? — bluevector, Jun 22 '12 at 14:11
You want to split the string. Check out http://stackoverflow.com/questions/2647/split-string-in-sql. — Gordon Linoff, Jun 22 '12 at 14:15
Dear lord that smells bad. Are the variable names and number of variables dynamic. If so, you'll need to use dynamic SQL to use the variables. If not, why would they be stored as a comma delimited string? Either way... — Nick Vaccaro, Jun 22 '12 at 14:17
The OP says he inherited this code, which likely means that it's been "running fine for years" according to the user. It's classic "Entity-Attribute-Value" design with a twist making it even uglier. I agree with @Norla, dynamic SQL will need to be used... — N West, Jun 22 '12 at 14:22
Its not that dynamic, for each row with the typevalue csv string, wherever I need to reference the type/value, I know exactly how many I should be expecting. — loyalflow, Jun 22 '12 at 14:23
I don't know why you people think parsing a string requires dynamic SQL. Could you post an answer that demonstrates the technique you think is needed? I agree that it's bad design but if it's on SQL Server 2000 still there's a good chance that that's what the OP is stuck with. — Aaron Bertrand, Jun 22 '12 at 14:28

Aaron Bertrand · Accepted Answer · 2012-06-22T14:30:21.700

Assuming you're stuck with bad design, this doesn't require dynamic SQL:

USE tempdb;
GO

CREATE TABLE dbo.foo(ID INT, TypeValue VARCHAR(1000));

INSERT dbo.foo SELECT 1, 'SomeID=1232,OtherID=3383';
INSERT dbo.foo SELECT 2, 'Blat=prong,SomeID=1254';
INSERT dbo.foo SELECT 3, 'Foo=bar,x=23';
INSERT dbo.foo SELECT 4, 'SomeID=767';
INSERT dbo.foo SELECT 5, 'x=y,SomeID=126,OtherID=3383';

SELECT ID, Value = SUBSTRING(TypeValue, 
  CHARINDEX('SomeID', TypeValue) + 7, 
  COALESCE(NULLIF(CHARINDEX(',', SUBSTRING(TypeValue, 
    CHARINDEX('SomeID', TypeValue) + 8, 1000)), 0), 1000))
FROM dbo.foo
WHERE ',' + TypeValue LIKE '%,SomeID=%';

Results:

ID  Value
--- -----
1   1232
2   1254
4   767
5   126

If you know which row you want:

DECLARE @ID INT, @Param VARCHAR(32);
SET @ID = 5;
SET @Param = 'SomeID';


DECLARE @val INT;

SELECT @val = SUBSTRING(TypeValue, 
  CHARINDEX(@Param, TypeValue) + LEN(@Param) + 1, 
  COALESCE(NULLIF(CHARINDEX(',', SUBSTRING(TypeValue, 
    CHARINDEX(@Param, TypeValue) + LEN(@Param) + 2, 1000)), 0), 1000))
FROM dbo.foo
WHERE ID = @ID 
AND ',' + TypeValue LIKE '%,' + @Param + '=%';

PRINT @val;

Result:

This doesn't guard against someone putting a string into the SomeID value, of course, so may be safer to only convert to an INT if it is proven to be numeric. But you get the point.

score 1 · Answer 2 · answered Jun 22 '12 at 15:17

Using split: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

DECLARE @Str NVARCHAR(4000)
SELECT @Str = 'SomeID=1232,OtherID=3383'
SELECT * into #temp FROM   [dbo].[Split] (@Str, ',') 

select property = (select Data from dbo.Split((select Data from #temp where Id = b.id), '=') where Id = 1),
       value = (select Data from dbo.Split((select Data from #temp where Id = b.id), '=') where Id = 2)
from #temp b

score 0 · Answer 3 · answered Feb 24 '22 at 20:34

SELECT [key] = LEFT(s.value, ca.pos - 1),
   [value] = SUBSTRING(s.value, ca.pos + 1, 8000)
FROM STRING_SPLIT(@activityValue, '|') s
CROSS APPLY (VALUES(CHARINDEX(':', s.value))) ca(pos);

Pulled from here: Is it possible to parse key:value pairs in SQL

Spliting a column that is a variable/value pair, and assigning a var in sql

3 Answers3