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:
>

Is this possible?

I have a procedure that uses a cursor. I want to use this procedure to build a table based on the cursor. How can I dynamically generate a table the will contain the results. I plan on executing this procedure to populate this table every week.
Thanks...create procedure...

declare my_cursor cursor for
select ...

open my_cursor
fetch next ... into <variables list>

while @.@.fetch_status = 0 begin
insert <table_name> values (<variables list>)
fetch next ... into ...
end

close my_cursor
deallocate my_cursor

returnsql

Is there any way to embed VB OCX control in SSRS ?

Is there any way to embed VB OCX control in SSRS. I have custom OCX control build in VB6 and want to deploy in SSRS. Can any one throw some light on it
Thanks

I have read some articles and learnt that, at present, there is no support for custom controls. If anybody know any information on this please provide here.

Is there an equivalent Replicate() function in Access

Hi,

I am trying to build a string from a datetime datetype and want to make sure that if a day or month is only one digit long then it is padded with a leading 0.

In SQL I can do it like this;

declare @.datetime datetime

select @.datetime = getdate()

select 'TPR'

+ replicate('0', 2 - len(datepart(dd,@.datetime))) + convert(varchar(2),datepart(dd,@.datetime))

+ replicate('0', 2 - len(datepart(mm,@.datetime))) + convert(varchar(2),datepart(mm,@.datetime))

+ convert(varchar(4),datepart(yyyy,@.datetime))

Do you know how I can do the same thing in Access?

Thanks for your help

It would be SO-O-O-O much easier to do it in this manner:

SELECT right( '00' + cast( datepart( day, @.DateTime ) AS varchar(2) ), 2 )

(Should work in Access AND SQL Server.)

But really, wouldn't something like this be a better solution: (this is SQL)

SELECT ( 'TPR' + SELECT replace( convert( varchar(10), @.DateTime, 101 ), '/', '' ))

For Access, you would have to use the FORMAT function instead of convert. Refer to the help for FORMAT().

|||

Yes Buddy.. You have it..

STRING(<number>, <Char>)

example:

String(5,"M") => MMMMM