I've got a items table. Each row for the reason that table matches just one product and it is recognized with a unique Id. Now each product might have multiple "codes" connected with this product. For instance:

Id         Code


0001      IN,ON,ME,OH

0002      ON,Mire,AC,ZO

0003      QA,PS,OO,ME

What I am attempting to do is produce a saved procedure to ensure that I'm able to pass inside a codes like "ON,ME" and also have it return best of luck that consists of the "ON" or "ME" code. Because the codes are comma separated, I'm not sure the way i can split individuals and check them. Is possible only using TSQL?

Edit: It is a mission critical table. I not have the authority to alter it.

You ought to be storing the codes inside a separate table, since you've got a many to a lot of relationship. Should you separate them, then you'll easily have the ability to check.

It might be easy to do in the kind of system you've now, but would require text searching from the posts, with multiple searches per row to operate, that will have huge performance problems as the data develops.

By trying to visit lower you current path : You'll have to falter your input string, because nothing guarantees the codes on each record have been in exactly the same order (or contiguous) because the input parameter. Then you would need to perform a

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

query by having an additional statement for each code you're checking for. Very inefficient.

The UDF idea below is another wise decision. However, with respect to the size your computer data and also the frequency of queries and updates, you might have issues there too.

will it be easy to create one more table that's stabilized that's synchronized on the scheduled basis (or with different trigger) that you should query against?

Everyone else appears very wanting to tell you just how you shouldn't do that, although I do not use whatever explicit reason behind why don't you.

Aside from smashing the normalization rules, this is because you'll perform a table-scan through all rows, because you can't come with an index around the individual "values" for the reason that column.

To put it simply, there is no method for the database engine to help keep some type of quick-listing of which rows consists of the code 'AC', unless of course you can either break up right into a separate table, or place it inside a column alone.

Now, for those who have other criteria inside your Choose claims which will limit the amount of rows lower with a workable number, then possibly this is ok, but otherwise I'd, if you're able to, avoid this solution and do what others have previously said, split up right into a separate table.

Now, if you are tied to this design, you are able to perform a search while using following kind of query:

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

This can:

  • Match 'ON,Mire,AC,ZO'
  • Not match 'ON,Mire,TAC,ZO'

I'm not sure when the 4g iphone is a practicable option inside your situation, should you have only 2-letter codes, you'll be able to use this:


However, this can perform horribly unless of course you limit the amount of rows using other criteria.

How you are storing data breaks normalization rules. Merely a single atomic value ought to be saved in every area. You need to store the items in one row.

Although all of the previous posters are correct concerning the normalization of the db schema, that you can do what you would like utilizing a "Table-Valued UDF" that can take a delimited string and returns a Table, with one row per value within the string... This can be used table while you would every other table inside your saved proc , joining into it, etc... this can solve your immediate problem...

Here is a connect to this type of UDF: FN_Split UDF

Even though article discusses utilizing it to pass through a delimited listing of data values in a saved proc, you should use exactly the same UDF to function on the delimited string saved inside a column of the existing table....