Is this syntax correct: (',' + @country_code + ',' like '%,' + rtrim(member_country)

I need some help in trying to build this SQL
select count(*)
from
tMember
where
(@.country_code is null
or (',' + @.country_code + ',' like '%,' + rtrim(member_country) + ',%'))
There is 1 record in tMember with member_country column's value = US.
@.country_code varchar(5)
1. This Returns 0 Count (Incorrect)
--
@.country_code = 'FR, US'
2. This Returns 0 Count (Incorrect)
--
@.country_code = 'FR,US,UK'
3. This Returns 0 Count (Incorrect)
--
@.country_code = 'FR,UK,US'
4. This Returns 1 Count (CORRECT)
--
@.country_code = 'US,FR'
How can I make the query return the right count even if US is not the first
element.
Oddly, if I hard code the values 'FR,US' in the SQL it returns the correct c
ount.
select member_id, member_country
from
tMember
where ',' + 'FR,US'+ ',' like '%,' + rtrim(member_country) + ',%'
TIA,
RajanYou say that @.country_code is varchar(5). Every one of the examples
you describe as Incorrect has a value for @.country_code longer than 5
characters. Perhaps making @.country_code long enough to hold the data
would help?
Also, given the embedded blank in the first example, consider removing
blanks using REPLACE:
or (',' + REPLACE(@.country_code,' ','') + ',' like '%,' +
rtrim(member_country) + ',%'))
Roy
On Sat, 25 Feb 2006 18:41:03 -0800, "Rajan" <roger@.yahoo.com> wrote:

>I need some help in trying to build this SQL
>select count(*)
>from
>tMember
>where
>(@.country_code is null
> or (',' + @.country_code + ',' like '%,' + rtrim(member_country) + ',%'))
>
>There is 1 record in tMember with member_country column's value = US.
>@.country_code varchar(5)
>
>1. This Returns 0 Count (Incorrect)
>--
>@.country_code = 'FR, US'
>
>2. This Returns 0 Count (Incorrect)
>--
>@.country_code = 'FR,US,UK'
>
>3. This Returns 0 Count (Incorrect)
>--
>@.country_code = 'FR,UK,US'
>
>4. This Returns 1 Count (CORRECT)
>--
>@.country_code = 'US,FR'
>
>How can I make the query return the right count even if US is not the first
element.
>
>Oddly, if I hard code the values 'FR,US' in the SQL it returns the correct
count.
>select member_id, member_country
>from
>tMember
>where ',' + 'FR,US'+ ',' like '%,' + rtrim(member_country) + ',%'
>TIA,
>Rajan|||>>
Perhaps making @.country_code long enough to hold the data
would help?
Yes Roy, that was it. I made it varchar(255) and it fixed the problem.
Thanks very much for your help.
Sincerely,
Rajan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message news:9k6202lum5vp1cklkg1hqbspmapd168km9
@.4ax.com...
> You say that @.country_code is varchar(5). Every one of the examples
> you describe as Incorrect has a value for @.country_code longer than 5
> characters. Perhaps making @.country_code long enough to hold the data
> would help?
>
> Also, given the embedded blank in the first example, consider removing
> blanks using REPLACE:
>
> or (',' + REPLACE(@.country_code,' ','') + ',' like '%,' +
> rtrim(member_country) + ',%'))
>
> Roy
>
>
> On Sat, 25 Feb 2006 18:41:03 -0800, "Rajan" <roger@.yahoo.com> wrote:
>