Comma separated values in a database field

Question

I have a products table. Each row in that table corresponds to a single product and it's identified by a unique Id. Now each product can have multiple "codes" associated with that product. For example:

Id     |    Code
----------------------
0001   |   IN,ON,ME,OH
0002   |   ON,VI,AC,ZO
0003   |   QA,PS,OO,ME

What I'm trying to do is create a stored procedure so that I can pass in a codes like "ON,ME" and have it return every product that contains the "ON" or "ME" code. Since the codes are comma separated, I don't know how I can split those and search them. Is this possible using only TSQL?

Edit: It's a mission critical table. I don't have the authority to change it.

Possible duplicate of [What is the opposite of GROUP\_CONCAT in MySQL?](https://stackoverflow.com/questions/17308669/what-is-the-opposite-of-group-concat-in-mysql) — philipxy, Jul 07 '19 at 20:04

Jason Coyne · Answer 1 · 2009-04-10T16:36:47.910

You should be storing the codes in a separate table, since you have a many to many relationship. If you separate them, then you will easily be able to check.

It would be possible to do in the type of system you have now, but would require text searching of the columns, with multiple searches per row to work, which will have huge performance problems as your data grows.

If you try to go down you current path : You will have to break apart your input string, because nothing guarantees the codes on each record are in the same order (or contiguous) as the input parameter. Then you would have to do a

Code LIKE '%IN%'
AND Code Like '%QA%'

query with an additional statement for every code you are checking for. Very inefficient.

The UDF idea below is also a good idea. However, depending on the size of your data and the frequency of queries and updates, you may have issues there as well.

would it be possible to create an additional table that is normalized that is synchronized on a scheduled basis (or based on a trigger) for you to query against?

Irawan Soetomo · Answer 2 · 2009-04-21T10:12:45.447

First, let's make the original table to become like this:


Id   | Value
-----+------
0001 | IN
0001 | ME
0001 | OH
0001 | ON
0002 | AC
0002 | ON
0002 | VI
0002 | ZO
0003 | ME
0003 | OO
0003 | PS
0003 | QA

It is accomplished by parsing the comma separated values into rows. Then use the powerful CROSS APPLY keyword to join with the original table to retrieve it's Id. Next step is simply to query this CTE.


create function FnSplitToTable
(
    @param nvarchar(4000)
)
returns table as
return
    with
    Num(Pos) as -- list of positions, numbered from 1 to 4000, largest nvarchar
    (
        select cast(1 as int)
        union all 
        select cast(Pos + 1 as int) from Num where Pos < 4000
    )
    select substring(@Param, Pos, 
        charindex(',', @Param + ',', Pos) - Pos) as Value
        from Num where Pos <= convert(int, len(@Param)) 
        and substring(',' + @Param, Pos, 1) = ','
go


create proc ProcGetProductId
(
    @Codes nvarchar(4000)
)
as
with
Src
(
    Id,
    Code
)
as
(
    select '0001', 'IN,ON,ME,OH'
    union all
    select '0002', 'ON,VI,AC,ZO'
    union all
    select '0003', 'QA,PS,OO,ME'
),
Parse as
(
    select 
        s.Id, 
        f.Value
    from 
        Src as s
    cross apply
        FnSplitToTable(s.Code) as f 
)
select distinct 
    p.Id
from 
    Parse as p
join
    FnSplitToTable(@Codes) as f
on
    p.Value = f.Value
option (maxrecursion 4000)
go

exec ProcGetProductId 'IN,ME' -- returns 0001 & 0003

of all the responses to this question, yours is the only *answer* — ninegrid, Apr 13 '09 at 14:44
of all the postings i made, you are the 1st to comment. thx! :) — Irawan Soetomo, Apr 16 '09 at 06:38

score 7 · Answer 3 · answered Apr 10 '09 at 16:35

Everybody else seems very eager to tell you that you should not do this, although I don't see any explicit explanation for why not.

Apart from breaking the normalization rules, the reason is that you'll do a table-scan through all rows, since you can't have an index on the individual "values" in that column.

Simply put, there's no way for the database engine to keep some kind of quick-list of which rows contains the code 'AC', unless you either break it up into a separate table, or put it in a column by itself.

Now, if you have other criteria in your SELECT statements that will limit the number of rows down to some manageable number, then perhaps this will be ok, but otherwise I would, if you can, try to avoid this solution and do what others have already told you, split it up into a separate table.

Now, if you're stuck with this design, you can do a search using the following type of query:

...
WHERE ',' + Code + ',' LIKE '%,AC,%'

This will:

Match 'ON,VI,AC,ZO'
Not match 'ON,VI,TAC,ZO'

I don't know if the last one is a viable option in your case, if you only have 2-letter codes, then you can use just this:

...
WHERE Code LIKE '%AC%'

But again, this will perform horribly unless you limit the number of rows using other criteria.

Not if that's all of my answer that you read, no, that is entirely correct. Please re-read my SQL and consider why I wrote it the way I did. — Lasse V. Karlsen, Apr 10 '09 at 16:53
Spoiler for inobservant readers: he adds commas to the start and end of the `Code` field, *then* he uses the LIKE predicate on it. — Bill Karwin, Apr 10 '09 at 17:41
And it will perform horribly, but not because of the fact that I add commas to the either side of the LIKE clause. — Lasse V. Karlsen, Apr 10 '09 at 18:18
Agreed. I call this pattern "Jaywalking" because it's trying to avoid the intersection! :-) — Bill Karwin, Apr 10 '09 at 19:46

Charles Bretana · Answer 4 · 2009-04-10T16:32:01.213

Although all the previous posters are correct about the normalization of your db schema, you can do what you want using a "Table-Valued UDF" that takes a delimited string and returns a Table, with one row per value in the string... You can use this table as you would any other table in your stored proc , joining to it, etc... this will solve your immediate issue...

Here's a link to such a UDF: FN_Split UDF

Although the article talks about using it to pass a delimited list of data values in to a stored proc, you can use the same UDF to operate on a delimited string stored in a column of an existing table....

score 4 · Answer 5 · answered May 22 '10 at 10:16

More than 1 year old question, but still thought it will be useful. You can use the FIND_IN_SET function of MySql. I am not sure whether other DBMSs support it or not.

You can use this function as follows:

SELECT * FROM `table_name` WHERE FIND_IN_SET('AC', `Code`) > 0

score 4 · Answer 6 · answered Apr 10 '09 at 16:19

4

The way you are storing data breaks normalization rules. Only a single atomic value should be stored in each field. You should store each item in a single row.

answered Apr 10 '09 at 16:19

Mehrdad Afshari

414,610
91
852
789

Unfortunately, I didn't design it. It already exists so I have just have to deal with it. – Apr 10 '09 at 16:20
If you are working on the system now, don't you have the authority to change it? Even if you do get a solution to your problem, it won't perform well for much more than small data sets. – JohnFx Apr 10 '09 at 16:24
I don't have the authority to change it. It's a mission critical table. – Apr 10 '09 at 16:29
I think you can create a user defined function to do so but I don't have SQL Server at this machine to test. Someone will probably come up with the solution. – Mehrdad Afshari Apr 10 '09 at 16:31

score 0 · Answer 7 · edited Jun 24 '15 at 11:55

1st Step : Code to create function

<font face="Courier New" size="2">
<font color = "blue">CREATE</font>&nbsp;<font color = "blue">FUNCTION</font>&nbsp;<font color = "maroon">[dbo]</font><font color = "silver">.</font><font color = "#FF0080"><b>[Udflistofids]</b></font>&nbsp;<font color = "maroon">(</font>
<br/><font color = "green"><i>&#45;&#45;&nbsp;Add&nbsp;the&nbsp;parameters&nbsp;for&nbsp;the&nbsp;function&nbsp;here</i></font>
<br/><font color = "#8000FF">@ListOfIDs</font>&nbsp;<font color = "blue">AS</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "maroon">max</font><font color = "maroon">)</font>
<br/><font color = "green"><i>&#45;&#45;,&nbsp;@IDsSeperationChar&nbsp;as&nbsp;varchar(5)&nbsp;=&nbsp;','</i></font>
<br/><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID1</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID2</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID3</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID4</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/><font color = "#8000FF">@UniqueID5</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/><font color = "maroon">returns</font>&nbsp;<font color = "#8000FF">@TabListOfIDs</font>&nbsp;<font color = "blue">TABLE</font>&nbsp;<font color = "maroon">(</font>
<br/>&nbsp;&nbsp;<font color = "green"><i>&#45;&#45;&nbsp;Add&nbsp;the&nbsp;column&nbsp;definitions&nbsp;for&nbsp;the&nbsp;TABLE&nbsp;variable&nbsp;here</i></font>
<br/>&nbsp;&nbsp;<font color = "maroon">id</font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">50</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;<font color = "maroon">uniqueid1</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;<font color = "maroon">uniqueid2</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;<font color = "maroon">uniqueid3</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;<font color = "maroon">uniqueid4</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;<font color = "maroon">uniqueid5</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">250</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/><font color = "blue">AS</font>
<br/>&nbsp;&nbsp;<font color = "blue">BEGIN</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "green"><i>&#45;&#45;&nbsp;Fill&nbsp;the&nbsp;table&nbsp;variable&nbsp;with&nbsp;the&nbsp;rows&nbsp;for&nbsp;your&nbsp;result&nbsp;set</i></font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">DECLARE</font>&nbsp;<font color = "#8000FF">@Pos</font>&nbsp;<font color = "blue">AS</font>&nbsp;<font color = "black"><i>INT</i></font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">DECLARE</font>&nbsp;<font color = "#8000FF">@ID</font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">AS</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">50</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "#8000FF">@IDsSeperationChar</font>&nbsp;<font color = "blue">AS</font>&nbsp;<font color = "black"><i>VARCHAR</i></font><font color = "maroon">(</font><font color = "black">5</font><font color = "maroon">)</font>&nbsp;<font color = "silver">=</font>&nbsp;<font color = "red">','</font>
<br/>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "green"><i>&#45;&#45;SET&nbsp;@ListOfIDs&nbsp;=&nbsp;REPLACE(&nbsp;@ListOfIDs,&nbsp;&nbsp;@IDsSeperationChar,&nbsp;',')</i></font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">SET</font>&nbsp;<font color = "#8000FF">@ListOfIDs</font>&nbsp;<font color = "silver">=</font>&nbsp;<font color = "fuchsia"><i>Ltrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>Rtrim</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "silver">+</font>&nbsp;<font color = "#8000FF">@IDsSeperationChar</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">SET</font>&nbsp;<font color = "#8000FF">@Pos</font>&nbsp;<font color = "silver">=</font>&nbsp;<font color = "fuchsia"><i>Patindex</i></font><font color = "maroon">(</font><font color = "red">'%'</font>&nbsp;<font color = "silver">+</font>&nbsp;<font color = "#8000FF">@IDsSeperationChar</font>&nbsp;<font color = "silver">+</font>&nbsp;<font color = "red">'%'</font><font color = "silver">,</font>&nbsp;<font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font>
<br/>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "green"><i>&#45;&#45;SET&nbsp;@Pos&nbsp;=&nbsp;CHARINDEX(@IDsSeperationChar,&nbsp;@ListOfIDs,&nbsp;1)</i></font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">IF</font>&nbsp;<font color = "fuchsia"><i>Replace</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font>&nbsp;<font color = "#8000FF">@IDsSeperationChar</font><font color = "silver">,</font>&nbsp;<font color = "red">''</font><font color = "maroon">)</font>&nbsp;<font color = "silver">&lt;&gt;</font>&nbsp;<font color = "red">''</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">BEGIN</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">WHILE</font>&nbsp;<font color = "#8000FF">@Pos</font>&nbsp;<font color = "silver">&gt;</font>&nbsp;<font color = "black">0</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">BEGIN</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">SET</font>&nbsp;<font color = "#8000FF">@ID</font>&nbsp;<font color = "silver">=</font>&nbsp;<font color = "fuchsia"><i>Ltrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>Rtrim</i></font><font color = "maroon">(</font><font color = "fuchsia"><i>LEFT</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font>&nbsp;<font color = "#8000FF">@Pos</font>&nbsp;<font color = "silver">-</font>&nbsp;<font color = "black">1</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "maroon">)</font>
<br/>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">IF</font>&nbsp;<font color = "#8000FF">@ID</font>&nbsp;<font color = "silver">&lt;&gt;</font>&nbsp;<font color = "red">''</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">BEGIN</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">INSERT</font>&nbsp;<font color = "blue">INTO</font>&nbsp;<font color = "#8000FF">@TabListOfIDs</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">(</font><font color = "maroon">id</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">uniqueid1</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">uniqueid2</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">uniqueid3</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">uniqueid4</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">uniqueid5</font><font color = "maroon">)</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">VALUES</font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">(</font><font color = "#8000FF">@ID</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "#8000FF">@UniqueID1</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "#8000FF">@UniqueID2</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "#8000FF">@UniqueID3</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "#8000FF">@UniqueID4</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "#8000FF">@UniqueID5</font><font color = "maroon">)</font>&nbsp;<font color = "green"><i>&#45;&#45;Use&nbsp;Appropriate&nbsp;conversion</i></font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">END</font>
<br/>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">SET</font>&nbsp;<font color = "#8000FF">@ListOfIDs</font>&nbsp;<font color = "silver">=</font>&nbsp;<font color = "fuchsia"><i>RIGHT</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "silver">,</font>&nbsp;<font color = "fuchsia"><i>Len</i></font><font color = "maroon">(</font><font color = "#8000FF">@ListOfIDs</font><font color = "maroon">)</font>&nbsp;<font color = "silver">-</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "fuchsia"><i>Len</i></font><font color = "maroon">(</font><font color = "#8000FF">@ID</font>&nbsp;<font color = "silver">+</font>&nbsp;<font color = "#8000FF">@IDsSeperationChar</font><font color = "maroon">)</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">)</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">SET</font>&nbsp;<font color = "#8000FF">@Pos</font>&nbsp;<font color = "silver">=</font>&nbsp;<font color = "fuchsia"><i>Patindex</i></font><font color = "maroon">(</font><font color = "red">'%'</font>&nbsp;<font color = "silver">+</font>&nbsp;<font color = "#8000FF">@IDsSeperationChar</font>&nbsp;<font color = "silver">+</font>&nbsp;<font color = "red">'%'</font><font color = "silver">,</font>&nbsp;<font color = "#8000FF">@ListOfIDs</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">)</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "green"><i>&#45;&#45;SET&nbsp;@Pos&nbsp;=&nbsp;CHARINDEX(@IDsSeperationChar,&nbsp;@ListOfIDs,&nbsp;1)</i></font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">END</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">END</font>
<br/>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "blue">RETURN</font>
<br/>&nbsp;&nbsp;<font color = "blue">END</font>
<br/>
<br/><font color = "maroon">go</font>&nbsp;
</font>


**2nd Step : Code to get the result**

<font face="Courier New" size="2">
<font color = "blue">DECLARE</font>&nbsp;<font color = "#8000FF">@udvMax</font>&nbsp;<font color = "black"><i>NVARCHAR</i></font><font color = "maroon">(</font><font color = "maroon">max</font><font color = "maroon">)</font>
<br/>
<br/><font color = "blue">SELECT</font>&nbsp;<font color = "#8000FF">@udvMax</font>&nbsp;<font color = "silver">=</font>&nbsp;<font color = "red">''</font>&nbsp;<font color = "silver">+</font>&nbsp;<font color = "fuchsia"><i>Substring</i></font><font color = "maroon">(</font>&nbsp;<font color = "maroon">(</font>&nbsp;<font color = "blue">SELECT</font>&nbsp;<font color = "red">'&nbsp;Union&nbsp;'</font>&nbsp;<font color = "silver">+</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "red">'Select&nbsp;*&nbsp;from&nbsp;dbo.udfListOfIDs('''</font>&nbsp;<font color = "silver">+</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">code</font>&nbsp;<font color = "silver">+</font>&nbsp;<font color = "red">''',&nbsp;'''</font>&nbsp;<font color = "silver">+</font>&nbsp;<font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">id</font>&nbsp;<font color = "silver">+</font>&nbsp;<font color = "red">''',&nbsp;'''</font>&nbsp;<font color = "silver">+</font>&nbsp;<font color = "maroon">tmpu</font><font color = "silver">.</font><font color = "maroon">code</font>&nbsp;<font color = "silver">+</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "red">''',&nbsp;null,null,null&nbsp;)'</font>&nbsp;<font color = "blue">FROM</font>&nbsp;<font color = "maroon">tmpu</font>&nbsp;<font color = "blue">FOR</font>&nbsp;<font color = "maroon">xml</font>&nbsp;<font color = "maroon">path</font><font color = "maroon">(</font><font color = "red">''</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "silver">,</font>&nbsp;<font color = "black">7</font><font color = "silver">,</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "black">200000</font><font color = "maroon">)</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "silver">+</font>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color = "red">'&nbsp;Order&nbsp;by&nbsp;UniqueID1,&nbsp;UniqueID2,&nbsp;UniqueID3,&nbsp;UniqueID4,&nbsp;UniqueID5,&nbsp;ID'</font>
<br/>
<br/><font color = "green"><i>&#45;&#45;Select&nbsp;@udvMax</i></font>
<br/><font color = "blue">EXECUTE</font>&nbsp;<font color = "#FF0080"><b>Sp_executesql</b></font>
<br/>&nbsp;&nbsp;<font color = "#8000FF">@udvMax</font>&nbsp;
</font>

****May be u may need to add your criteria in select statement in 2nd step.**

Hope this will help you.

JP

score 0 · Answer 8 · edited May 23 '17 at 12:17

0

Although in your case simple LIKE will work, here is the solution how to parse comma separated strings Table Normalization (Parse comma separated fields into individual records).

edited May 23 '17 at 12:17

Community

1
1

answered Oct 12 '12 at 18:16

Alex Aza

76,499
26
155
134

score 0 · Answer 9 · edited Sep 23 '14 at 09:45

if you want to do it with php and mysql it can be any number of keyword no restriction

$var = explode(',',"ahmad,sayeed,asmal,babu");
$query = "SELECT * FROM post WHERE post_tags LIKE '%a%' "; 
$query1=NULL;
foreach($var as  $value)
{   
    $query1.= " OR post_tags LIKE '%$value%' ";
}

echo "$query  $query1";

OUTPUT:

SELECT * FROM post WHERE post_tags LIKE '%a%' OR post_tags LIKE '%ahmad%' OR post_tags LIKE '%sayeed%' OR post_tags LIKE '%asmal%' OR post_tags LIKE '%babu%'

score 0 · Answer 10 · answered Apr 10 '09 at 16:19

This might not be possible if you're stuck with that database design, but it would be a lot easier to put the codes into separate records in another table:

ProductCode
-----------
ProductID (FK to Product.ID)
Code (varchar)

The table might look like this:

ProductID    Code
-----------------
0001         IN
0001         ON
0001         ME
...

The query would look something like this (you'd have to pass in the Codes somehow - either as separate variables, or maybe a comma-separated string that you split in the proc):

select ProductID
from ProductCode
where Code in ('ON', 'ME')

score 0 · Answer 11 · answered Apr 21 '09 at 10:38

I agree with other posters here that you should look carefully into schema normalization, but I also know that shortcuts are part of life.

Here's a sample function written in Sybase dialect that does what you do:

ALTER FUNCTION "DBA"."f_IsInStringList"( IN @thisItem char(2), IN @thisList varchar(4000) )
RETURNS INTEGER
DETERMINISTIC
BEGIN


DECLARE is_member bit;
DECLARE LOCAL TEMPORARY TABLE tmp (thisItem  char(2)) ;
DECLARE @tempstring varchar(10);
DECLARE @count integer;

IF LENGTH(TRIM(@thisList)) > 0 THEN

    WHILE LENGTH(TRIM(@thisList)) > 0  LOOP
       -- loop over comma-separated list and stuff members into temp table
       IF LOCATE ( @thisList, ',' , 1) > 0 THEN

           SET @count = LOCATE ( @thisList, ',' , 1);
           SET @tempstring = SUBSTRING ( @thisList, 1,@count-1 );

           INSERT INTO tmp ( thisItem  ) VALUES (  @tempstring );
           SET @thisList = STUFF ( @thisList, 1, @count, '' )

        ELSE

            INSERT INTO tmp ( thisItem  ) VALUES ( @thisList );
            SET @thisList = NULL;

        END IF;

    END LOOP ;

END IF;

IF EXISTS (SELECT * FROM tmp WHERE thisItem   = @thisItem ) THEN
    SET is_member = 1;
ELSE
    SET is_member = 0 ;
END IF ;

    RETURN is_member;
END

You can then build a simple query to check whether a value occurs in your comma-separated string:

select * from some_table t 
         WHERE f_IsInStringList('OR', t.your_comma_separated_column) = 1 OR
               f_IsInStringList('ME', t.your_comma_separated_column) = 1

Comma separated values in a database field

11 Answers11

Linked