In SQL how to split an select a string which is no

问题:

Here is my query I used to get UserID present in both where condition and TSTable

                    SELECT  UserID
                    FROM    TSTable
                    WHERE UserID IN (@UserID) 

I need to to get the UserId from @UserID

  where    @UserID = ''IMS080'',''IMS108'',''IMS218''

which are not present in TSTable. How can I do this? For example, if I Execute the Query it should return value from IN condition 'IMS080' which is not present in TSTable.

Thanks in Advance

回答1:

Is this what you're looking for?

select 'IMS080' as UserID
  union
select 'IMS108'
  union
select 'IMS218';

回答2:

Try this

;WITH CTE AS 
(
    SELECT Split.a.value('.', 'VARCHAR(100)') AS USERID  
    FROM 
    (
        SELECT CAST ('<M>' + REPLACE('IMS080,IMS108,IMS218', ',', '</M><M>') + '</M>' AS XML) AS Data  
    )AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
)
SELECT * FROM CTE
WHERE USERID NOT IN (SELECT UserId FROM #TSTable)