T-SQL Code for IN Array Select statement

[ccding]

Crystal Reports XI; SQL Server DB;

I need the T-SQL code to select
all companies that are IN certain Regions.
Keep in mind that the field, Region, is an array and holds more than one region.

EXAMPLE DATA for the 2 fields below:
CompanyA Region
Comp1 Europe; Asia;
Comp2 Eastern Europe; India;
Comp3 Europe;
Comp4 USA; North Asia

EXPECTED OUTPUT when filtering on 'Region = Europe or Region = Eastern Europe'
CompanyA Region
Comp1 Europe; Asia;
Comp2 Eastern Europe; India;
Comp3 Europe;

How do I code using T-SQL?
Thank you.

 

[lbass]

I'm not sure about T-SQL, but if you were coding this in the CR record selection area, you could create a parameter {?region} and then enter:

{?region} in {table.region} //where {table.region} is your array

In a command, you could use:

table.`region` like '%'+'{?Region}'+'%'

-LB

 

[ccding]

Thanks LB.
In command my code is:
select
table1.propertyvalue as Region
from
table1
where
table1.propertyvalue like '%'+'{?Region}'+'%'
---
I am then prompted to enter value,
however I then get the error:
'Argument data type sql_variant is invalid for argument 1 of like function.'

I need to perfrom this function using a Command.
Thanks again for your help.

 

[lbass]

I'm guessing this message might mean that propertyvalue is not a string. {table1.propertyvalue} doesn't look like a likely candidate to hold region info--are you sure it does? You did create the parameter {?Region} within the command, right (to the right of the query area)?

-LB

 

[ccding]

Yes, parameter is created, named: Region.
I updated the SQL command to:
select
cast(table1.propertyvalue as varchar) as Region
from
table1
where
table1.propertyvalue like
'%'+'{?Region}'+'%'
___________
Same error. I thought perhaps to change the datatype of PropertyValue to varchar. Though it is a string.
Perhaps the issue is due to the fact that more than 1 element can be in this array?
--------------
Within Crystal Reports Record Selection, I can create a parameter, Region, then select:
{?Region} in {UserDefinedPropertyValue.PropertyValue}.
This works successfully. But I really need to translate this into Command code.
Any ideas?

 

[lbass]

Is your table literally called table1 or are you just showing that in the thread to simplify? I notice you referring to {UserDefinedPropertyValue.PropertyValue}--is this from a database table? Or? I'm pretty much out of ideas. Maybe someone else can add to this.

-LB

 

[ccding]

Oh Yes, sorry. Trying to simplify.
Yes I tried to illustrate table.field by terming in table1.propertyvalue.
------------