query a table not in normal 3rd form

Question

Hi I have a table which was designed by a lazy developer who did not created it in 3rd normal form. He saved the arrays in the table instead of using MM relation . And the application is running so I can not change the database schema.

I need to query the table like this:

SELECT * FROM myTable WHERE usergroup = 20

where usergroup field contains data like this : 17,19,20 or it could be also only 20 or only 19.

I could search with like: SELECT * FROM myTable WHERE usergroup LIKE 20 but in this case it would match also for field which contain 200 e.g.

Anybody any idea? thanx

Answer 1

Write a table-valued user-defined function (UDF in SQL Server, I am sure it will have a different name in other RDBMS) to parse the values of the column containing the list which is stored as a string. For each item in the comma-delimited list, your function should return a row in the table result. When you are using a query like this, query against the results returned from the UDF.

Answer 2

Fix the bad database design.

A short-term fix is to add a related table for the correct structure. Add a trigger to parse the info in the old field to the related table on insert and update. Then write a script to [parse out existing data. Now you can porperly query but you haven't broken any of the old code. THen you can search for the old code and fix. Once you have done that then just change how code is inserted or udated inthe orginal table to add the new table and drop the old column.

Answer 3

Write a function to convert a comma delimited list to a table. Should be pretty simple. Then you can use IN().