Jacob Turley Jacob Turley - 2 months ago 11
SQL Question

SQL concat / + operator behaving strangely?

So basically to explain my situation I have a program where a user can select code numbers, that are alpha numeric. These codes are stored in my SQL database as datatype char.

When they select all the codes they want, the program then sends a few parameters(the codes being one of them). The codes are strung together and look something like this:

',01,1,A3' etc. etc. with commas separating the codes. I have the comma in front, but changing the comma to the back does not change anything.

the @reasonCode variable is the reason codes strung together.

In my where clause I have a statement that is this:

(@reasonCode = 'ALL') OR
((@reasonCode <> 'ALL' AND (charindex(',' + ro_reason_code, @reasonCode) > 0)))


Basically I want to restrict my results to just those that have those specific reason codes the user selected(among other parameters). I am trying to achieve that by stringing together the codes, and then searching through them using charindex, seperated by commas.

However I am running into an issue. Here are the results using a few different variations of reason codes:

',1' = 625 records (correct number)
',01' = 1015(correct number)
',01,1 = 1640(correct number)
',1,01' = 1015(for whatever reason it isn't picking up the 1 reason codes)


That is my issue right there.

When I put the 1 in front of the 01, it doesn't pick up the 1 reason codes. But if I do it flip-flopped it works fine...

Any ideas as to why this happens?
(I have tried also using the concat function and get the same results, and also tried forcing everything to be char datatype.)

In the end I would like the same result, regardless if it is ,01,1 or ,1,01.

Answer

I'm pretty sure this is because you said you're using the char type instead of varchar. Try replacing your charindex expression with this:

charindex(',' + rtrim(ro_reason_code), @reasonCode)

When I used a type of char(2) in the table and char(16) for the @reasonCode, I could reproduce your result, and I found that adding the rtrim fixed the problem. But unfortunately I can't explain exactly what's going here, why having ',1' at the end of the string should work without the trim whereas having it at the beginning does not. Hopefully someone can provide a more in-depth answer that gets into the "why," but I thought I'd still post this for the time being to get you running.

Reproduction:

-- Forgive the "hackish" way of populating this table. I'm assuming sysobjects has >=1015 records.
declare @Code table (ro_reason_code char(2));
insert @Code select top 625 '1' from sysobjects;
insert @Code select top 1015 '01' from sysobjects;

declare @reasonCode char(16); 

set @reasonCode = ',1,01';
select count(1) from @Code where @reasonCode = 'ALL' or charindex(',' + ro_reason_code, @reasonCode) > 0;        -- Result: 1015
select count(1) from @Code where @reasonCode = 'ALL' or charindex(',' + rtrim(ro_reason_code), @reasonCode) > 0; -- Result: 1640

set @reasonCode = ',01,1';
select count(1) from @Code where @reasonCode = 'ALL' or charindex(',' + ro_reason_code, @reasonCode) > 0;        -- Result: 1640
select count(1) from @Code where @reasonCode = 'ALL' or charindex(',' + rtrim(ro_reason_code), @reasonCode) > 0; -- Result: 1640