CODEWITHSUNDEEP
mysqlsql
mattew
mattew

Reputation: 203

Excluding a value within a range of values defined by "between" in a mysql query

I'm wondering if it is possible to exclude a value inside a range defined by "between".

Here is an example:

 ...
 WHEN left(name,2) between 'AA' and 'AZ' then 'HALLO'
 ...

I want to exclude from this range, for example, the value 'AM' Is there a short way to obtain this or I need to split the range into two different ones as follow?

 ...
 WHEN left(name,2) between 'AA' and 'AL' then 'HALLO'
 WHEN left(name,2) between 'AN' and 'AZ' then 'HALLO'
 ...

Thanks in advance.

Upvotes: 1

Views: 1313

Answers (3)

John Woo
John Woo

Reputation: 263803

You it is possible:

...
WHEN (left(name,2) between 'AA' and 'AZ') AND (left(name,2) <> 'AM') 
     then 'HALLO'
...

Upvotes: 1

bfavaretto
bfavaretto

Reputation: 71939

WHEN left(name,2) between 'AA' and 'AZ' AND NOT left(name,2) = 'AM' then 'HALLO'

Upvotes: 2

mathematical.coffee
mathematical.coffee

Reputation: 56935

Just put your more restrictive case first:

CASE 
WHEN left(name,2) == 'AM' then 'wohoo'
WHEN left(name,2) between 'AA' and 'AZ' then 'HALLO'
END

If it matches 'AM' then since that case is first in the statement, its action will be taken, even though the second case also matches 'AM'.

Upvotes: 3

Related Questions