sql server programming: SELECT from list -- sql server programming

SELECT from list

spitapps
11/6/2006 11:44:23 PM

sql server programming: I need to create a stored procedure that takes a list of product
numbers(541, 456, CE6050,...) and selects only the customers that have
all product numbers in the list. I have a product table that has the
following schema:

rowid numeric(18,0),
productNumber numeric(5,0),
customerNumber numeric(5,0)

and a customer table:

customerNumber numeric(5,0)
address varchar(50)
contact varchar(50)

So a customer can have more than one product, but I need a select
statement that takes the product numbers and selects them against the
product table and returns only the customerNumbers that have the entire
list of product numbers. Thanks in advance for any help.

Re: SELECT from list

Uri Dimant
11/7/2006 12:00:00 AM

Take a look at this

CREATE FUNCTION dbo.CleanChars
(@str VARCHAR(8000), @validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @validchars + ']%',@str) > 0
SET @str=REPLACE(@str, SUBSTRING(@str ,PATINDEX('%[^' + @validchars +
']%',@str), 1) ,'')
RETURN @str
END

GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)

INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')

SELECT namestr,
dbo.CleanChars(namestr,'0-9')
FROM sometable

drop table sometable
drop function dbo.CleanChars

[quoted text, click to view]