0

I'm trying to query my sql database to return all the rows where the ID is contained in a separate tables column. The list of project IDs is kept in the Feedback table in the Project_ID Column with datatype varchar. I am trying to return the rows from the Projects table where the IDs are kept in the Project_ID column with datatype varchar.

I am doing this using the query

SELECT * FROM Projects WHERE Project_ID IN (
    SELECT Project_ID FROM Feedback WHERE ID = 268 and Project_ID IS NOT NULL
)

When I run this query I am returned with the message:

Conversion failed when converting the varchar value '36;10;59' to data type int
Tom
  • 9,725
  • 3
  • 31
  • 48
  • SQL Server has two data types *designed* for holding multiple values. Those are XML and *tables*. If you insist on not using either of them and instead stuffing values inside strings, you're going to increasingly encounter issues, because SQL Server doesn't understand the conventions that you invent. – Damien_The_Unbeliever Feb 04 '16 at 15:44
  • @Damien_The_Unbeliever I realise this isn't a very well designed database and it desperately needs restructuring but at the moment this is the situation that I am in. There is a plan to redevelop it in Q2 of this year but until then I'm stuck with this. – Tom Feb 04 '16 at 15:45
  • [this](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) may help you. – Viplock Feb 04 '16 at 15:49

1 Answers1

2

This is yet another example of the importance of normalizing your data.
Keeping multiple data points in a single column is almost never the correct design, and by almost never I mean about 99.9999%.

If you can't normalize your database, you can use a workaround like this:

SELECT * 
FROM Projects p
WHERE EXISTS (
    SELECT Project_ID 
    FROM Feedback F WHERE ID = 268 
    AND Project_ID IS NOT NULL
    AND ';'+ F.Project_ID +';' LIKE '%;'+ CAST(p.Project_ID as varchar) +';%'
)

You can't use the IN operator since it's expecting a list of values delimited by a comma, while you try to supply it with a single value that is delimited by a semicolon. Even if the values in Project_ID was delimited by a comma it would still not work.

The reason I've added the ; on each side of the Project_ID in both tables is that this way the LIKE operator will return true for any location it finds the Projects.Project_Id inside the Feedback.Project_Id. You must add the ; to the Projects.Project_Id to prevent the LIKE to return true when you are looking for a number that is a partial match to the numbers in the delimited string. Consider looking for 12 in a string containing 1;112;455 - without adding the delimiter to the search value (12 in this example) the LIKE operator would return true.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121