SQL String Array Code

[ccding]

SQL Server DB;

Hi all, can anyone help me with this SQL Select statement?

I have a Srring Array field named: Region.
(The array can contain up to 4 elements.)
What is the SQL code to select records that are IN a specific region?

Example of Current output:
CompName Region
CompA NA; Europe;
CompB Eastern Europe;
CompC Europe; Eastern Europe; Asia; Other
CompD Asia;
CompE Europe
CompF NA; Asia

If I wanted to retrieve companies that are IN region(s) of: Europe or Eastern Europe, what would the SQL code be?

Expected Output for Region(s) IN Europe or IN Eastern Europe
CompName
CompA
CompB
CompC
CompE

Thanks again for all of your time and thought on this one.

 

[SkipVought]

Hi,

Select CompName, Region
From [The Table]
Where Region In ('Europe','Eastern Europe')

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[mrdenny]

That's not going to work. IN doesn't do LIKE comparisons. LIKE will only return row B.

This is the problem with storing more than one value within a text field. It becomes very had to parse through that data.

For the best result you should make some changes to the database design do one of the following.

1. Add a Region table and a CompanyRegions table which makes the many to many relationship between the Region and Company tables.

2. Create a Region table, but instead of using an identity value for the it use a bitwise value. This is a number that doubles for each value in the table. The values would look something like this.

1	NA
2	Europe
4	Eastern Europe
8	Asia

Then in the Company table add up the values for the countries which the company is in. You can then search this field using the Bitwise operator.

select *
from Company
where Country & 2 = 2
    and Country & 4 = 4

If you can't make these changes because it's a vendor app then you will need to search the field using the LIKE operator. This will not perform well as index scans must be performed. Because you have two regions with the word Europe in it this method can give you issues. If you wanted to pull Europe but not Eastern Europe this could get tricky.

select *
from Company
where Region like '%Europe%'
   or region like '%Asia%'

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLSister]

I'm with Denny, you should not store your data this way. It violates the most basic rule of database design which is never store more than one piece of information in a field.

If you cannot change the structure, for performance reasons it might be best to split the data using FN_split (google for the code) and put it in a different table of it's own. Then use a trigger to mainatain that table in the face of changes to the data. The fn_split will help you move the data to a correct relational table as well if you have the ability to change your structure.

"NOTHING is more important in a database than integrity." ESquared

 

[ccding]

Thanks gang !

 

[SkipVought]

Duh! I didn't even notice the strung together items.

Clean up your data! DITTO!

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]