comparing variable to a field with comma-separated values

20 pts.
Tags:
Comma Separated Values
SQL Server 2000
SQL Server development
"m trying to compare a variable (@item) to a field which has multiple item numbers separated by commas. I've used "LIKE" and "IN" to no avail. I've also tried appending wildcard characters to the variable (@item2), which also hasn't worked. Full-text indexing is not activated on the DB (SQL Server 2000), so I can't use "CONTAINS". So, in the code below, I"m trying to compare @item which may look like "0282" to ItemString which may look like "0004,0089,0282". ItemString is varchar(255). If ItemString only has one item number in it, it will work using "IN". Any takers? CREATE PROCEDURE dbo.pGetOrderLotData @order char(8), @item char(5), @package char(20) output, @sodship char(30) output, @soistring varchar(60) output, @soqstring varchar(60) output, @item2 varchar(7) output AS set nocount on -- DECLARE @item2 varchar(6) SET @item2 = '%' + @item + '%' Drop Table tCLsearchorder1 Create Table tCLsearchorder1 (sopackage char(8), soorder char(8), sodateship datetime, soitemstring varchar(60), soqtystring varchar(60)) Select @order = OrderNumber, @package = PackageNumber, @sodship = DateShipped, @soistring = ItemString, @soqstring = QuantityString From tblPackage Where OrderNumber = @order AND @item2 LIKE ItemString -- may have to use shipdate instead of @item --SELECT DATEPART(mm, @sodship) + "/" + DATEPART(dd, @sodship) + "/" + DATEPART(yyyy, @sodship) AS '@sodship' IF @package IS NULL Begin Set @package = 'Invalid Order#!' GOTO nomatch End Else Insert INTO tCLsearchorder1 (sopackage, soorder, sodateship, soitemstring, soqtystring) VALUES (@package, @order, @sodship, @soistring, @soqstring) nomatch: GO 




Software/Hardware used:
SQL Server 2000

Answer Wiki

Thanks. We'll let you know when a new response is added.

<b><i>One way</i></b> to do it could be using a condition like this:

WHERE (ItemString = @item
OR ItemString LIKE @item + ‘,%’
OR ItemString LIKE ‘%,’ + @item + ‘,%’
OR ItemString LIKE ‘%,’ + @item)

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Caruncles wrote: Sorry, didn't use proper formatting. I"m trying to compare a variable (@item) to a field which has multiple item numbers separated by commas. I've used "LIKE" and "IN" to no avail. I've also tried appending wildcard characters to the variable (@item2), which also hasn't worked. Full-text indexing is not activated on the DB (SQL Server 2000), so I can't use "CONTAINS". So, in the code below, I"m trying to compare @item which may look like "0282" to ItemString which may look like "0004,0089,0282". ItemString is varchar(255). If ItemString only has one item number in it, it will work using "IN". Any takers?
     CREATE PROCEDURE dbo.pGetOrderLotData @order char(8),
     @item char(5),
     @package char(20) output,
     @sodship char(30) output,
     @soistring varchar(60) output,
     @soqstring varchar(60) output,
     @item2 varchar(7) output 
     
     AS
     
     set nocount on
     -- DECLARE @item2 varchar(6)
     SET @item2 = '%' + @item + '%' 
     
     Drop Table tCLsearchorder1
     
     Create Table tCLsearchorder1
     (sopackage char(8),
     soorder char(8),
     sodateship datetime,
     soitemstring varchar(60),
     soqtystring varchar(60))
     
     Select @order = OrderNumber,
     @package = PackageNumber,
     @sodship = DateShipped,
     @soistring = ItemString,
     @soqstring = QuantityString
     From tblPackage Where OrderNumber = @order AND @item2 LIKE ItemString
     
     -- may have to use shipdate instead of @item
     
     --SELECT DATEPART(mm, @sodship) + "/" + DATEPART(dd, @sodship) + "/" + DATEPART(yyyy, @sodship) AS '@sodship'
     
     IF @package IS NULL
     Begin 
     Set @package = 'Invalid Order#!'
     GOTO nomatch
     End
     Else
     
     Insert INTO tCLsearchorder1 (sopackage, soorder, sodateship, soitemstring, soqtystring)
     VALUES (@package, @order, @sodship, @soistring, @soqstring)
     
     
     nomatch:
     GO 
     
    69,160 pointsBadges:
    report
  • philpl1jb
    Hi I 'm not an expert but I think that this with the value 0282 SET @item2 = ‘%’ + @item + ‘%’ would build the string %0282 % and search for 0282 space. perhaps @item2 = ‘%’ + trim(@item) + ‘%’ then this statement looks backwards @item2 LIKE ItemString should be ItemString LIKE @item2
    49,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following