parse a value from a field with commas

Question

parse a value from a field with commas

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184395 More actions · Answer 1

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

Caruncles Hall of Fame Points: 3299 More actions · Answer 2

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."

Steve Jones - SSC Editor SSC Guru Points: 739555 More actions · Answer 3

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.