0

I have a small problem I could tackle quite easily in C# but I have been asked to do it within the SQL.

I have a Stored procedure which takes in an int as a parameter, and I need to check if that parameter is inside the value of a colon seperated column in the database.

(
                                (
                                    gvf_permitted_projects is null
                                ) 
                                or 
                                (   -- @activeProject IN gvf_permitted_projects
                                    -- @activeProject = 11
                                    -- gvf_permitted_projects = '11:17'
                                )
                            )

This is inside the where clause of my SELECT, I could do this in C# with minimal effort but I'm not too sure how to do it here. Do I need to use a temp table and then do a select into that?

Alan Gordon
  • 353
  • 5
  • 14
  • 1
    How is the colon separated column in the database, any specific order followed. A sample input data with case would help. – Abhishek Nov 23 '15 at 12:57
  • 2
    You could use a custom split T-SQL function. Checkout this answer http://stackoverflow.com/questions/10914576/t-sql-split-string – cleftheris Nov 23 '15 at 12:59
  • 1
    it's just as shown in the comments, 11:17 The numbers chosen there are just examples. But I would want it to return true as 11 is in the lsit – Alan Gordon Nov 23 '15 at 13:00
  • If I used a split string function? Could I implement that within my where clause? – Alan Gordon Nov 23 '15 at 13:01
  • I am guessing this query might just work, just check the same in your `WHERE` clause- `PATINDEX('%'+CAST(@activeProject AS VARCHAR(10))+'%',@gvf_permitted_projects)` – Abhishek Nov 23 '15 at 13:10
  • You should fix your database structure so you are not storing integers as strings, and you only have one value in a column. In this case, you want to use a junction table. – Gordon Linoff Nov 23 '15 at 13:58

3 Answers3

0

So you would have to split your string into columns. If you're not allowed to use functions, you can use this: Splitting Delimited Strings Using XML in SQL Server

Quick demo how does this work:

DECLARE @xml AS XML
    , @str AS VARCHAR(100)
    , @delimiter AS VARCHAR(1)

SET @str = '11:17'
SET @delimiter = ':'
SET @xml = cast(('<X>' + replace(@str, @delimiter, '</X><X>') + '</X>') AS XML)

SELECT N.value('.', 'varchar(10)') AS value
FROM @xml.nodes('X') AS T(N)

It brings back this:

╔═══════╗
║ value ║
╠═══════╣
║    11 ║
║    17 ║
╚═══════╝

So this would be your SQL:

DECLARE @YourTable TABLE
(
    Id INT
    , gvf_permitted_projects VARCHAR(100)
);

INSERT INTO @YourTable (Id, gvf_permitted_projects)
VALUES (1, '11:17')
    , (2, '11:13')
    , (3, '12:17');

SELECT *
FROM @YourTable AS YT
CROSS APPLY (
    SELECT CAST(('<X>' + replace(gvf_permitted_projects , ':', '</X><X>') + '</X>') AS XML)
    ) AS V(N)
WHERE EXISTS (
        SELECT 1
        FROM N.nodes('X') AS T(N)
        WHERE N.value('.', 'INT') = 11
        );

That's result:

╔════╦════════════════════════╦════════════════════╗
║ Id ║ gvf_permitted_projects ║         N          ║
╠════╬════════════════════════╬════════════════════╣
║  1 ║ 11:17                  ║ <X>11</X><X>17</X> ║
║  2 ║ 11:13                  ║ <X>11</X><X>13</X> ║
╚════╩════════════════════════╩════════════════════╝

Although, I'd strongly recommend to normalize your data structure. This is just a workaround.

Evaldas Buinauskas
  • 13,739
  • 11
  • 55
  • 107
0

You can use like operator with some text manipulation, although this will be somewhat slow and normalised structure would be preferred anyway:

':'+@gvf_permitted_projects+':' like '%:'+cast(@activeProject as varchar(20))+':%'
Arvo
  • 10,349
  • 1
  • 31
  • 34
0

In this answer I added up some nice tricks you can do with XML and string values:

https://stackoverflow.com/a/33658220/5089204

Go to the "Dynamic IN" section. Hope this helps.

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114