CODEWITHSUNDEEP
sqlselect
Nicholas Aysen
Nicholas Aysen

Reputation: 580

How to find int values from a comma separated varchar column SQL

I have 3 tables. Transactions, LineItems which is a child of the Transactions table, and Rejections

Currently in the lineitems table there is a column called rejectioncodes, which contains the id values of the rejection table but in a string value, separated by commas.

enter image description here

How would I be able to select a row where the id is in that varchar?

My code sample so far

select transactions.fTransactionID 
from Transactions inner join
     LineItems
     on transactions.fTransactionID = lineitems.fTransactionID 
where fTransactionStatusID = 11 and
      LineItems.fRejectionCodes in (select fRejectionCodeID
                                    from RejectionCodes 
                                    where fRejectionCodeID in (1,7,8,9,12,13,15)
                                   _

Of course my query will say it can't be done due to the column being a varchar

Upvotes: 0

Views: 1577

Answers (2)

SMS
SMS

Reputation: 84

Use STUFF() with FOR XML PATH('')as follows Which gives you the same comma seperated result.


SELECT  STUFF((SELECT  ',' + INSTITUTIONNAME
            FROM EDUCATION EE
            WHERE  EE.STUDENTNUMBER=E.STUDENTNUMBER
            ORDER BY sortOrder
        FOR XML PATH('')), 1, 1, '') AS listStr

FROM EDUCATION E
GROUP BY E.STUDENTNUMBER

Upvotes: 0

Benkirane Aziz
Benkirane Aziz

Reputation: 56

As said in the comment a design change will be a good idea but for the comma separated problem it is better explained in the link bellow :

http://www.databasejournal.com/features/mssql/converting-comma-separated-value-to-rows-and-vice-versa-in-sql-server.html

you can use this to break your comma separated data into rows and then work on it

Upvotes: 1

Related Questions