To find a substring matching separated by commas

Question

I have a table say "user"which is having a col "access" having multi values separated by comma. and i have another table " codes" which has a column "SCRCODES" having some user codes as single valued. so i need to check whether the multi values in the col "access" of the table "user" is having any of the values present in the "SCRCODES" col of the table "codes"

someone please advise on this. Thanks

So SCRCODES will have only a single code one per row I mean? — Jens Krogsboell, Sep 10 '14 at 07:45
Yes Ankit is right. Do not store values that reference another table in a comma separated column. You will regret it later. Performance will be bad and the code will be complex. Instead create a mapping table. — alexander zak, Sep 10 '14 at 07:47
possible duplicate of [How do I split a string so I can access item x](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — Bulat, Sep 10 '14 at 08:11

score 0 · Answer 1 · answered Sep 10 '14 at 08:50

i think this will help you:

 ALTER FUNCTION [dbo].[Split]
        (
          @RowData NVARCHAR(MAX) ,
          @SplitOn NVARCHAR(5)
        )
    RETURNS @ReturnValue TABLE ( Data NVARCHAR(MAX) )
    AS 
        BEGIN
            DECLARE @Counter INT
            SET @Counter = 1 
            WHILE ( CHARINDEX(@SplitOn, @RowData) > 0 ) 
                BEGIN  
                    INSERT  INTO @ReturnValue
                            ( data
                            )
                            SELECT  Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1,
                                                                 CHARINDEX(@SplitOn,
                                                                  @RowData) - 1)))
                    SET @RowData = SUBSTRING(@RowData,
                                             CHARINDEX(@SplitOn, @RowData) + 1,
                                             LEN(@RowData)) 
                    SET @Counter = @Counter + 1  
                END 
            INSERT  INTO @ReturnValue
                    ( data )
                    SELECT  Data = LTRIM(RTRIM(@RowData))  
            RETURN  
        END;

    GO

    DECLARE @str VARCHAR(MAX)
    SET @str = select access from users where oid = "1"
    SELECT *
    FROM codes c, users u where c.SCRCODES in dbo.Split(@str, ',')

score 0 · Answer 2 · answered Sep 10 '14 at 08:54

0

I assume that your sercodes does not contain comma.

You can do something like this:

select sercodes from codes
inner join users
on user.codeid = codes.codeid
where charindex(sercodes + ',', access) > 0 or charindex(',' + sercodes , access) > 0

The idea is that access will be stored like this way "read, write, execute". So, it will be either end with comma or start with comma and part of the string..

Please let me know whether it is working. You can give actual table data and design to get more accurate query.

answered Sep 10 '14 at 08:54

masum7

822
6
17

why are we using access and char index here..dint undertstand – user3797411 Sep 10 '14 at 12:07
how to compare a data in the cursor variable with a column data from table? – user3797411 Sep 10 '14 at 12:08

To find a substring matching separated by commas

2 Answers2