using "LIKE" comparisons in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using "LIKE" comparisons in SQL

Author

Topic

verybrightstar
Starting Member

16 Posts

Posted - 2004-04-23 : 04:07:59

hi all,
I have a table that called Zone_Area

Table Name : Zone_Area
-----------------------
zonecode | zonearea
-----------------------
100 | EW27;NS2;NS3;EW21;EW25;NS4;EW23;EW20;EW22;CE02;EW26;EW19;EW18
101 | EW1;EW2;EW4;EW6;EW7;

I have tried using this query below to extract zonearea that falls between EW1-EW12.
SELECT * FROM Zone_Area WHERE zonearea LIKE '%EW[1-12]%' .

when i run the query it suppose to show only the second record which is only zonecode 101. The "LIKE" comparions seems to compare anything that has EW2% or EW1% as a results two records are shown.

I tried this two query it seems not able to work
SELECT * FROM Zone_Area WHERE zonearea LIKE '%EW[<=12]%' .
SELECT * FROM Zone_Area WHERE zonearea LIKE '%EW[<13]%' .

Can i know what query to have to extract only zonearea that falls from EW1-EW12?

kt

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-04-23 : 04:29:37

Redesign your datamodel. You shall never store multiple values in a column.


create table zone_area(zone_code int, zone char(?), area int,
primary key(zone_code,zone,area))

and your query becames very simple


select code,... from zone_code
where zone_code in (select zone_code from zone_area
  where zone = 'EW' 
    and area between 1 and 12

With the current design all queries will be slow as indexes can not be used.

verybrightstar
Starting Member

16 Posts

Posted - 2004-04-23 : 04:46:14

quote:
Originally posted by LarsG

Redesign your datamodel. You shall never store multiple values in a column.

create table zone_area(zone_code int, zone char(?), area int,
primary key(zone_code,zone,area))

and your query becames very simple

select code,... from zone_code
where zone_code in (select zone_code from zone_area
where zone = 'EW'
and area between 1 and 12

With the current design all queries will be slow as indexes can not be used.

hi LarsD , thanks for your solutions i do intend to create another column that called area because it i have more than 1000 records that can't possibly to do that.

kt

Kristen
Test

22859 Posts

Posted - 2004-04-23 : 06:16:26

quote:
zonecode | zonearea
-----------------------
100 | EW27;NS2;NS3;EW21;EW25;NS4;EW23;EW20;EW22;CE02;EW26;EW19;EW18
101 | EW1;EW2;EW4;EW6;EW7;
SELECT * FROM Zone_Area WHERE zonearea LIKE '%EW[1-12]%' .

Apart from redesigning your DB

, I think the following will work as a temporary measure


SELECT * FROM Zone_Area WHERE 
   zonearea + ';' LIKE '%EW[1-9];%   -- Find EW1 to EW9
OR zonearea + ';' LIKE '%EW1[0-2];%  -- Find EW10 to EW12

The [XXXX] is a set of characters to match where [0-5] is shorthand for [012345]. You can also use [^012345] to match any character which is NOT in the 012345 list.

But that's it - the rest of the Regular Expression syntax got left out :-( Or at least, that's all I know

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources