September 15, 2011 at 4:54 am
Hi All,
I have a table with the following structure:
ID Data
1 1;2;3
2 1;3
3 1;4
I want a stored procedure that will take a parameter as a string (e.g 1;3) and then will return all non duplicate rows which has data containing 1 or 3.
Any help on this will be appreciated.
September 15, 2011 at 5:33 am
Is there just one column DATA? Or do you mean 3 columns (or more) which may contain NULL values?
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
September 15, 2011 at 5:40 am
Hi,
Try this. This may be help you something
create proc usp_idtest
(@var varchar(10))
as
begin
select distinct * from idtest
where ( PATINDEX('%'+left(@var,1)+'%', data) > 0
or PATINDEX('%'+right(@var,1)+'%', data) > 0 )
end
exec usp_idtest '1;3'
September 15, 2011 at 6:02 am
Assuming I've read your requirements properly (your requirements are insane btw, sounds more like an academic problem than a real world one). . .
First, lets create some sample data: -
DECLARE @table AS TABLE (ID INT, Data VARCHAR(5))
INSERT INTO @table
SELECT 1, '1;2;3'
UNION ALL SELECT 2, '1;3'
UNION ALL SELECT 3, '1;4'
UNION ALL SELECT 4, '3;4'
UNION ALL SELECT 5, '4;5'
Now, lets have a look at the sproc. We need to split that data up so we can find out if the values from your string are contained in any of the "data".
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
Now lets look at your actual code.
--Declare the input string for the sproc
DECLARE @inputString VARCHAR(10)
--Set as "1;3", so we're looking for anywhere in the data
--where a 1 or a 3 is contained
SET @inputString = '1;3'
SELECT test.ID, test.Data
FROM @table test
--Split the "data" column, so we can compare with the string
CROSS APPLY dbo.DelimitedSplit8k(test.Data,';') split
--Split the string so we can compare with the newly split data
CROSS APPLY (SELECT Item
FROM dbo.DelimitedSplit8k(@inputString,';') a
--Does the "data" column contain a 1 or a 3?
WHERE a.Item = split.Item) input
--Group together because the splitter has split each "ID" into
--seperate bits of information depending on what the "data" column
--holds
GROUP BY test.ID, test.Data
So, we're expecting all except the 5th ID to be returned. . .
ID Data
----------- -----
1 1;2;3
2 1;3
3 1;4
4 3;4
(4 row(s) affected)
Yay.
September 15, 2011 at 7:12 am
And add distinct to return only unique values.
edit: sorry, missed the group by
edit 2: the requirement "non duplicate rows" is not met yet. You could try something like this:
SELECT t.ID, t.Data
from (
SELECT test.ID, test.Data, ROW_NUMBER() OVER (PARTITION BY test.DATA ORDER BY test.ID) as NR
FROM @table test
--Split the "data" column, so we can compare with the string
CROSS APPLY dbo.DelimitedSplit8k(test.Data,';') split
--Split the string so we can compare with the newly split data
CROSS APPLY (SELECT Item
FROM dbo.DelimitedSplit8k(@inputString,';') a
--Does the "data" column contain a 1 or a 3?
WHERE a.Item = split.Item) input
--Group together because the splitter has split each "ID" into
--seperate bits of information depending on what the "data" column
--holds
GROUP BY test.ID, test.Data
) t
WHERE t.NR = 1
September 15, 2011 at 7:44 am
R.P.Rozema (9/15/2011)
And add distinct to return only unique values.
edit: sorry, missed the group byedit 2: the requirement "non duplicate rows" is not met yet. You could try something like this:
SELECT t.ID, t.Datafrom (
SELECT test.ID, test.Data, ROW_NUMBER() OVER (PARTITION BY test.DATA ORDER BY test.ID) as NR
FROM @table test
--Split the "data" column, so we can compare with the string
CROSS APPLY dbo.DelimitedSplit8k(test.Data,';') split
--Split the string so we can compare with the newly split data
CROSS APPLY (SELECT Item
FROM dbo.DelimitedSplit8k(@inputString,';') a
--Does the "data" column contain a 1 or a 3?
WHERE a.Item = split.Item) input
--Group together because the splitter has split each "ID" into
--seperate bits of information depending on what the "data" column
--holds
GROUP BY test.ID, test.Data
) t
WHERE t.NR = 1
I get no duplicate results with the code I showed, that was what the "GROUP BY" was for, eliminating the duplicates created by the splitter function from the final result set.
September 15, 2011 at 8:18 am
It's not duplicates introduced by the splitter that should be eliminated, it's duplicates in the input that need to be removed per the requirement.
Try this for your input:
DECLARE @table AS TABLE (ID INT, Data VARCHAR(5))
INSERT INTO @table
SELECT 1, '1;2;3'
UNION ALL SELECT 2, '1;3'
UNION ALL SELECT 3, '1;4'
UNION ALL SELECT 4, '3;4'
UNION ALL SELECT 5, '4;5'
UNION ALL SELECT 6, '1;3'
And you'll see the output having duplicates:
ID Data
----------- -----
1 1;2;3
2 1;3
6 1;3
3 1;4
4 3;4
(5 row(s) affected)
I would also propose to rewrite the query as:
SELECT t.ID, t.Data
FROM (
SELECT test.ID, test.Data, ROW_NUMBER() OVER (PARTITION BY test.DATA ORDER BY test.ID) NR
FROM @table test
) t
WHERE t.NR = 1
AND EXISTS (
SELECT *
FROM dbo.DelimitedSplit8k(t.Data,';') split
cross join dbo.DelimitedSplit8k(@inputString,';') a
WHERE a.Item = split.Item
)
edit: Added suggestion to use exists instead of group by.
September 15, 2011 at 8:54 am
R.P.Rozema (9/15/2011)
It's not duplicates introduced by the splitter that should be eliminated, it's duplicates in the input that need to be removed per the requirement.
Ah, reading fail. I didn't spot that in the OPs requirements.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply