I have a nvarchar column that contains some comma deliminated data eg 1,12,35,6. I need to split the ones that are comma deliminated in order to query with a look up table that contains the text version of the number..
but I cant seem to get it right.even if it vreats another table that contains the the EOI_Enquiry_ID (to remain relational) and ProjectType_ID columns so I can link that table??
any help will be most appreciated
ALTER FUNCTION CMA_projects.SplitString (@sep char(1), @s varchar(512))
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
WHERE stop > 0
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
as for the query
WITH CTE AS (SELECT ProjectType_ID
WHERE (ProjectType_ID IS NOT NULL))
FROM CTE AS CTE_1
CROSS APPLY CMA_projects.SplitString(ProjectType_ID,',')