CODEWITHSUNDEEP
sqldb2db2-400
Lokeshkumar R
Lokeshkumar R

Reputation: 615

db2 between query for char columns

I have a scenario where there is a table like below

Table Name:Employee

id EmpName(char 4) -always 4 chars only
1  ARUN
2  BALA
3  LOKI

I wanted a Db2 query which should get me the range of employees who's name starting with A to D. Meaning employees whose name starts with A,B,C and D should be displayed

I have tried this query 

select * from employee where empname between 'A000' and 'DZZZ'

Its not working properly

Upvotes: 0

Views: 816

Answers (4)

jarlh
jarlh

Reputation: 44766

Switch to 'AAAA' as start value.

where empname between 'AAAA' and 'DZZZ'

Will perform much better, if empname is indexed.

ANSI SQL compliant and portable, should work fine on both DB2 and Postgresql.

EDIT:

If empnames shorter than 4 characters will be stored, you can change to:

where empname between 'A' and 'DZZZ'

(Will not miss employee 'A'.)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Use the following version:

select e.*
from employee e
where e.empname >= 'A' and
      e.empname < 'E'

This does exactly what you are asking for -- and making no assumptions about the second character. In addition, it is index-safe, if you have an index on empname (although you are selecting so many rows that indexes probably are not relevant).

Upvotes: 2

Jeevitha
Jeevitha

Reputation: 117

Try this too

select empname from employee where empname
REGEXP'^[ABCD]'

Upvotes: 0

Jeevitha
Jeevitha

Reputation: 117

Try this

select empname from employee where empname like 'A%' or 
empname like 'B%' or empname like 'C%' or 
empname like 'D%' 
order by empname

Upvotes: 0

Related Questions