Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-01 : 05:52:23
|
CREATE TABLE testTab ( id int, name varchar(10))insert into testTab values(1, 'chandu'), (2, 'abcd'),(4, 'xxx')I will pass set of values as comma separated (Eg: 2,3,5)Then it has to display the ids35That means the ids which are not existed in the table--Chandu |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-02-01 : 06:32:14
|
--CREATE TABLE testTab ( id int, name varchar(10))--insert into testTab values(1, 'chandu'), (2, 'abcd'),(4, 'xxx')select dt.number as id from (select number from master..spt_values where type = 'p' and number in (2,3,5))dtleft join testTab t on t.id = dt.numberwhere t.id is null Too old to Rock'n'Roll too young to die. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-01 : 06:38:55
|
hi webfred,Those ids are not real... They may 2428492,437939, 23242 like this--Chandu |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-02-01 : 06:40:34
|
Where do they come from? Is there a chance to have them in a table? How much numbers we are talking about? Too old to Rock'n'Roll too young to die. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-01 : 06:42:39
|
so many values. they are getting ids from front-end.. they don't want to declare a temp table also--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-01 : 06:44:46
|
[code]CREATE TABLE testTab ( id int, name varchar(10))insert into testTab values(1, 'chandu'), (2, 'abcd'),(4, 'xxx'),(437939,'gfergerg')DECLARE @list varchar(100)SET @list='2,3,5,2428492,437939, 23242'SELECT *FROM(SELECT m.n.value('.','int') AS idFROM (SELECT CAST('<Node><Row>' + REPLACE(@list,',','</Row><Row>') + '</Row></Node>' AS xml ) AS x) tCROSS APPLY x.nodes('/Node/Row')m(n))mWHERE NOT EXISTS(SELECt 1 FROM testTab WHERE id=m.id)output-------------------------id-----------35242849223242[/code]EDIT: Added your latest data as well. it will work for any id values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-01 : 06:54:16
|
Thank you visakh...--Chandu |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-02-01 : 06:54:41
|
declare @array varchar(1000)set @array = '2,3,5'select col1 as idfrom(select * from dbo.fnParseArray(@array,','))dtleft join testTab t on t.id = dt.col1where t.id is null-- the function is like thisCREATE FUNCTION [dbo].[fnParseArray] (@array VARCHAR(max),@separator CHAR(1))RETURNS @T Table (occ int,col1 varchar(50))AS BEGINDECLARE @separator_position INT DECLARE @array_value VARCHAR(max)declare @occurence int =1 if (left(@array,1)=@separator)begin set @array=stuff(@array,1,1,'')endif (right(@array,1)<>@separator)begin SET @array = @array + @separatorend-- Loop through the string searching for separtor charactersWHILE PATINDEX('%' + @separator + '%', @array) <> 0 BEGIN SELECT @separator_position = PATINDEX('%' + @separator + '%',@array) SELECT @array_value = LEFT(@array, @separator_position - 1) INSERT into @T VALUES (@occurence,@array_value) set @occurence = @occurence + 1 SELECT @array = STUFF(@array, 1, @separator_position, '')ENDRETURN END Too old to Rock'n'Roll too young to die. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-02-01 : 06:55:13
|
Too old to Rock'n'Roll too young to die. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-01 : 07:04:07
|
quote: Originally posted by bandi Thank you visakh...--Chandu
welcome ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|