| Author |
 Topic  |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
 Posted - 02/01/2013 : 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
ids
3
5
That means the ids which are not existed in the table
--
Chandu |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 02/01/2013 : 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))dt
left join testTab t on t.id = dt.number
where t.id is null
Too old to Rock'n'Roll too young to die. |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/01/2013 : 06:38:55
|
hi webfred,
Those ids are not real... They may 2428492,437939, 23242 like this
--
Chandu |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 02/01/2013 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/01/2013 : 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
India
47023 Posts |
Posted - 02/01/2013 : 06:44:46
|
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 id
FROM (SELECT CAST('<Node><Row>' + REPLACE(@list,',','</Row><Row>') + '</Row></Node>' AS xml ) AS x) t
CROSS APPLY x.nodes('/Node/Row')m(n)
)m
WHERE NOT EXISTS(SELECt 1 FROM testTab WHERE id=m.id)
output
-------------------------
id
-----------
3
5
2428492
23242
EDIT: Added your latest data as well. it will work for any id values
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 02/01/2013 06:47:09 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/01/2013 : 06:54:16
|
Thank you visakh...
--
Chandu |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 02/01/2013 : 06:54:41
|
declare @array varchar(1000)
set @array = '2,3,5'
select col1 as id
from
(select * from dbo.fnParseArray(@array,','))dt
left join testTab t on t.id = dt.col1
where t.id is null
-- the function is like this
CREATE FUNCTION [dbo].[fnParseArray] (@array VARCHAR(max),@separator CHAR(1))
RETURNS @T Table (occ int,col1 varchar(50))
AS
BEGIN
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(max)
declare @occurence int =1
if (left(@array,1)=@separator)
begin
set @array=stuff(@array,1,1,'')
end
if (right(@array,1)<>@separator)
begin
SET @array = @array + @separator
end
-- Loop through the string searching for separtor characters
WHILE 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, '')
END
RETURN
END
Too old to Rock'n'Roll too young to die. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 02/01/2013 : 06:55:13
|
Too old to Rock'n'Roll too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/01/2013 : 07:04:07
|
quote:
Originally posted by bandi
Thank you visakh...
--
Chandu
welcome 
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Problem ( SELECT statement)
