August 14, 2009 at 7:30 am
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
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
August 14, 2009 at 7:58 am
Believe it or not your problem actually has a simple solution once you know the answer. You have defined the @item parameter as CHAR(5), yet you are only inserting a 4 character string '0282'. SQL Server is padding the string to '0282 ' so when you try to use LIKE the LIKE expression becomes '%0282 %' and the data in ItemString does not contain a space/blank. You can fix this several ways:
Change the @item parameter to VARCHAR(n)
Change the LIKE to ItemString LIKE '%' + LTRIM(RTRIM(@item)) + '%'
Here's some verification code:
DECLARE @table TABLE(item_string VARCHAR(255))
DECLARE @item CHAR(5)
DECLARE @item_char4 CHAR(4)
DECLARE @item_varchar5 VARCHAR(5)
SELECT
@item = '0282',
@item_char4 = @item, -- this truncates the blank
@item_varchar5 = @item_char4 -- have to use char(4) or you get the blank
INSERT INTO @table (
item_string
) VALUES (
'0004,0089,0282' )
SELECT
*
FROM
@table T
WHERE
T.item_string LIKE '%' + @item + '%'
-- works
SELECT
'TRIM Variable' AS TYPE,
*
FROM
@table T
WHERE
T.item_string LIKE '%' + LTRIM(RTRIM(@item)) + '%'
-- works
SELECT
'CHAR(4) Variable' AS TYPE,
*
FROM
@table T
WHERE
T.item_string LIKE '%' + @item_char4 + '%'
-- works
SELECT
'VARHCAR(5) Variable' AS TYPE,
*
FROM
@table T
WHERE
T.item_string LIKE '%' + @item_varchar5 + '%'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 14, 2009 at 8:06 am
Jack, Thanx for the reply. I"ll try to work with this, but I won't know what the values of the variables are. I'm reading them from a table.
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
August 14, 2009 at 8:07 am
I like the first suggestion from Jack, if that fits your business need. You want to be sure you won't have issues if the data grows to 5 (or more) characters.
August 14, 2009 at 8:57 am
There may be another solution as well. Could you please post the DDL for the tables involved (not just the ones in the procedure), sample data for the tables, and expected results based on the sample data. If you read and follow the instructions in the first article I have referenced below in my signature block, we can actually provide you with tested code that you can then work with.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply