Prev: Q: Linked Server and Stored Procedure
Next: Drop all table and repopulate
From: Alex on 1 Jul 2008 06:51
Hello
Use SQL Server 2000

Given a table which holds string data ( could be characters as well)
If user provide with value let me say '88' I return the data where this
value is occured (col= 4,5,7 for example in my case)

However if user does not provide a value I need to return all data where
occured 23 and 56 (col=1,4,6)

I'm really intrested with set-based solutuion .




CREATE TABLE #Test
(
col INT,
col1 VARCHAR(100)
)
INSERT INTO #Test VALUES (1,'12,23,44,56')
INSERT INTO #Test VALUES (2,'26,45,22,32')
INSERT INTO #Test VALUES (3,'45,22,34,23')
INSERT INTO #Test VALUES (4,'88,56,34,23')
INSERT INTO #Test VALUES (5,'88')
INSERT INTO #Test VALUES (6,'23,55,56,44')
INSERT INTO #Test VALUES (7,'88,55,56,24')




My query i got so far is not perfect as if you change 56(as for 56 it does
not return col=4) to 44 it does return a wrong result


DECLARE @t VARCHAR(100)
SET @t =''
SELECT *
FROM #test
WHERE ','+col1+',' like '%,'+case @t WHEN '88'THEN @t+',%'
ELSE '23,%%,56,%' END


Thanks


From: Plamen Ratchev on 1 Jul 2008 08:52
Here is one way. In the query below the reference to the system table
master..spt_values is used to simulate auxiliary table with numbers. In
production replace with real table
(http://www.projectdmx.com/tsql/tblnumbers.aspx).


DECLARE @search_value VARCHAR(10);

SET @search_value = '88';

SELECT col
FROM (
SELECT col,
SUBSTRING(col1, n, CHARINDEX(',', col1 + ',', n ) - n ) AS
list_value,
n + 1 - LEN(REPLACE(LEFT(col1, n ), ',', '')) AS list_position
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 250) AS Nums(n)
CROSS JOIN #Test
WHERE SUBSTRING(',' + col1, n, 1) = ','
AND n < LEN(col1) + 1) AS T
WHERE list_value = @search_value
OR (@search_value IS NULL AND list_value IN ('23', '56'))
GROUP BY col
HAVING COUNT(*) = 2
OR @search_value IS NOT NULL;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Alex on 1 Jul 2008 09:02
Hi Plamen
Thanks, I was also thinking abouth such solutuions
When I do not specify value your query returns nothing but it should reurn
all occurance of 56 and 23

DECLARE @search_value VARCHAR(50)

SET @search_value=''





"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:D6EADE3C-E6A7-435C-91D1-6D680DD06116(a)microsoft.com...
> Here is one way. In the query below the reference to the system table
> master..spt_values is used to simulate auxiliary table with numbers. In
> production replace with real table
> (http://www.projectdmx.com/tsql/tblnumbers.aspx).
>
>
> DECLARE @search_value VARCHAR(10);
>
> SET @search_value = '88';
>
> SELECT col
> FROM (
> SELECT col,
> SUBSTRING(col1, n, CHARINDEX(',', col1 + ',', n ) - n ) AS
> list_value,
> n + 1 - LEN(REPLACE(LEFT(col1, n ), ',', '')) AS list_position
> FROM (SELECT number
> FROM master..spt_values
> WHERE type = 'P'
> AND number BETWEEN 1 AND 250) AS Nums(n)
> CROSS JOIN #Test
> WHERE SUBSTRING(',' + col1, n, 1) = ','
> AND n < LEN(col1) + 1) AS T
> WHERE list_value = @search_value
> OR (@search_value IS NULL AND list_value IN ('23', '56'))
> GROUP BY col
> HAVING COUNT(*) = 2
> OR @search_value IS NOT NULL;
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 1 Jul 2008 09:10
I assumed you meant when the search value is nothing then it is NULL. Try it
with this:

SET @search_value = NULL;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Plamen Ratchev on 1 Jul 2008 09:56
Alternatively you can change the query to look for empty string instead of
NULL:

SELECT col
FROM (
SELECT col,
SUBSTRING(col1, n, CHARINDEX(',', col1 + ',', n ) - n ) AS
list_value,
n + 1 - LEN(REPLACE(LEFT(col1, n ), ',', '')) AS list_position
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 250) AS Nums(n)
CROSS JOIN #Test
WHERE SUBSTRING(',' + col1, n, 1) = ','
AND n < LEN(col1) + 1) AS T
WHERE list_value = @search_value
OR (@search_value = '' AND list_value IN ('23', '56'))
GROUP BY col
HAVING COUNT(*) = 2
OR @search_value <> '';

HTH,

Plamen Ratchev
http://www.SQLStudio.com

 |  Next  |  Last
Pages: 1 2
Prev: Q: Linked Server and Stored Procedure
Next: Drop all table and repopulate