November 18, 2008 at 6:44 pm
Hello all....
I have a field with comma-separated multiple values. I want to write a sproc where I pass a single value parameter to select entire row if the parameter value is contained in the values of the the given field.
Any ideas please?
Thanks.
Sanya
November 18, 2008 at 9:10 pm
Yep... see the following
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2008 at 10:04 pm
Hi There,
If you have for example this value in your row 1,2,3,4,5 or 'Ab','cd','efg' I suppose the column will be a varchar or char in this case you can use LIKE '%value%' or in ('value')
so the sql stmt will be if exists (SELECT COLUMNAME1 FROM tEMP WHERE Column1 in ('AD'))
BEGIN
INSERT STMT
or
SELECT STMT
END
I hope that helps...
Razi, M.
http://questivity.com/it-training.html
November 19, 2008 at 2:32 am
I hope this might be of use, we use the following function to breakdown the delimited list and then pass in our sp using cusors;
/* THIS FUNCTION IS RELATED TO CORE.funcGetItemFromDelimitedList */
/* THE SCALAR FUNCTION RETURNS A SPECIFIED POSITIONAL ITEM FROM A NVARCHAR LIST DELIMITED BY A GIVEN CHARACTER */
/* THIS FUNCTION RETURNS A SQL SERVER TABLE FROM A NVARCHAR LIST DELIMITED BY A KNOWN CHARACTER
E.G. "JOHN, PAUL, GEORGE, RINGO", WHERE THE LIST IS SIMPLY DELIMITED USING COMMAS. */
/* THE FUNCTION TAKES TWO PARAMETERS / ARGUMENTS
-AN EXPRESSION EVALUATING TO THE LIST TO BE PROCESSED
- THE CHARACTER USED TO SEPARATE THE ITEMS IN THE LIST
*/
/* USING THE EXAMPLE ABOVE, THE FUNCTION RETURNS A ONE COLUMN TABLE WITH 4 ROWS, CONTAINING THE VALUES
JOHN
PAUL
GEORGE
RINGO
*/
/* EXAMPLE OF USE OF FUNCTION */
/* SELECT * FROM func('John; Paul; George; Ringo; Yoko',';')
RETURNS
John
Paul
George
Ringo
Yoko
*/
CREATE FUNCTION [CORE].[funcGetTableFromDelimitedList]
(
@myDelimitedList NVARCHAR(MAX),
@mySeparator NVARCHAR(255)
)
RETURNS @tblList TABLE (vchItem NVARCHAR(MAX))
AS
BEGIN
DECLARE @mySeparatorPosition INT;
DECLARE @myExtractedValue NVARCHAR(255);
WHILE LTRIM(@myDelimitedList) <> N''
/* WHILE BATCH */
BEGIN
/* GET POSITION OF FIRST SEPARATOR */
SELECT @mySeparatorPosition = (SELECT CHARINDEX(@mySeparator,@myDelimitedList))
IF @mySeparatorPosition = 0
/* RETURN THE WHOLE LIST IF NO SEPARATOR FOUND AND INSERT INTO TABLE */
BEGIN
SELECT @myExtractedValue = LTRIM(@myDelimitedList)
INSERT INTO @tblList VALUES(@myExtractedValue)
BREAK
END
/* SET FIRST ITEM LIST */
SELECT @myExtractedValue = LEFT(@myDelimitedList,@mySeparatorPosition-1)
/* CHANGE THE INPUT PARAMETER TO REMOVE THE FIRST LIST ITEM */
SELECT @myDelimitedList = LTRIM(SUBSTRING(@myDelimitedList,@mySeparatorPosition+1,99999))
/* CHECK EXTRACTED VALUE */
IF COALESCE(@myExtractedValue,N'') = N''
CONTINUE
/* INSERT INTO TABLE IF NOT EMPTY */
ELSE
INSERT INTO @tblList VALUES(@myExtractedValue)
END
RETURN
END
November 19, 2008 at 8:33 pm
Alasdair Thomson (11/19/2008)
I hope this might be of use, we use the following function to breakdown the delimited list and then pass in our sp using cusors;
Please see the following... cursors are not the way to go here... heh... nor anyplace else, actually.
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply