Split colon separated key value in SQL

Question

I am passing a keyword to my SP say @Keyword. This keyword may include

param1:value1 param2:value2

param1:value1 

param2:value2

How can I can grab value1 and value2?

Give Some Example Datas , so it will easy to give u the Exact answer — Hell Boy, May 11 '15 at 07:49
So u need to grab 'abc' and 'Good' separately right? Give me Example value u passing in @keyword. — Hell Boy, May 11 '15 at 07:52
Can I be the bad guy here, and ask why you want to do it this way? Use a table valued parameter. — Bridge, May 11 '15 at 07:59
Don't pass composite arguments to a stored procedure like that. use a table value parameter. [read this.](http://stackoverflow.com/a/24881759/3094533) — Zohar Peled, May 11 '15 at 08:16

score 3 · Accepted Answer · edited May 11 '15 at 08:15

You can use .nodes for xml type and string functions like LEFT / CHARINDEX / SUBSTRING to achieve this

Query

DECLARE @v VARCHAR(MAX) = 'param1:value1 param2:value2'

DECLARE @xml xml = '<x>' + REPLACE(@v,' ','</x><x>') + '</x>'
;WITH CTE AS 
(
SELECT c.value('.','NVARCHAR(MAX)') as val
FROM @xml.nodes('x') t(c)
)
SELECT
LEFT(val,CHARINDEX(':',val)-1),
SUBSTRING(val,CHARINDEX(':',val)+1,LEN(val)-CHARINDEX(':',val))
FROM cte

If possible, you should use static parameters. if not I would suggest you can use Table valued parameters or xml directly as a parameter.

Note: The above code assumes that <x>,</x> and ' ' space are not valid param or value.

Here is another way,

            ALTER FUNCTION [dbo].[GetValueByKey]
            (
                @Key nvarchar(max)
                ,@Str nvarchar(max)
            )
            RETURNS NVARCHAR(MAX)
            AS
            BEGIN

                DECLARE @Result nvarchar(max) = '';
                DECLARE @KeyIndex int = 0; 
                DECLARE @SpaceIndex int = 0; 

                SELECT @KeyIndex = CHARINDEX(@Key + ':', @Str);

                IF(@KeyIndex > 0)
                BEGIN
                    SET @KeyIndex = @KeyIndex + LEN(@Key) + 1;
                    SET @Result = SUBSTRING(@Str, @KeyIndex, LEN(@Str) - @KeyIndex + 1);
                    SELECT @SpaceIndex = CHARINDEX(' ', @Result);
                    IF(@SpaceIndex <= 1)
                    BEGIN
                        SET @SpaceIndex = LEN(@Result)  +1;
                    END
                    SELECT @Result = SUBSTRING(@Result, 0, @SpaceIndex)
                END

                RETURN @Result;
            END

well if space is also allowed as a value, how will splitting `param1:value1 param2:value2` work? — ughai, May 11 '15 at 08:11
Since your answer is fundamentally different than mine, I would suggest you to create a new answer or even better add it in your question as an edit and mention that this is what you have tried so far. — ughai, May 11 '15 at 09:18

score 2 · Answer 2 · answered May 11 '15 at 07:50

2

How about just putting param1 and param2 as parameters to your stored procedure?

CREATE PROCEDURE procedurename
    @param1   datatype
  , @param2   datatype
AS
  content here
GO

answered May 11 '15 at 07:50

Toby

66
2

score 2 · Answer 3 · answered May 11 '15 at 08:03

Here is another way using a split function. See this article by Jeff Moden for reference.

Basically, you want to split your @keyword using ' ' (space) as the delimiter. Then using some string functions such as LEFT, SUBSTRING, and CHARINDEX, you can extract the param and its value.

DECLARE @keyword VARCHAR(8000)
SELECT @keyword = 'param1:value1 param2:value2'

;WITH CteSpace AS(
    SELECT *
    FROM dbo.DelimitedSplit8K(@keyword, ' ')
)
SELECT
    Param = LEFT(Item, CHARINDEX(':', Item) - 1),
    Value = SUBSTRING(Item, CHARINDEX(':', Item) +1, LEN(Item) - CHARINDEX(':', Item))
FROM CteSpace

score 1 · Answer 4 · answered May 11 '15 at 08:21

Don't use a delimited string, use a tabled value parameter instead.
Create a user defined table type that contains 2 columns: paramName and value:

CREATE TYPE Keywords AS Table
(
    Keyword_ParamName varchar(10), -- or whatever length that suits your needs
    Keyword_value varchar(200), -- or whatever length that suits your needs
)

Then simply declare the @keyword parameter as this type:

CREATE PROCEDURE stp_doWhatever 
(
    @Keyword dbo.Keywords READONLY -- Note: Readonly is a must!
)
AS
-- do whatever

You can use the @keyword as a table in the stored procedure to perform selects, joins, whatever you need.

Split colon separated key value in SQL

4 Answers4