For different application cases, you may need to change how values are represented because the operator used can deal only with a specific representation of values.
The following functions are available for this purpose: CHR(a,n), NUM(a), UPPER/LOWER(x), ASCII(x), MAPCHAR(x,n,i), ALPHA(x,n), HEX(a), CHAR(a,t), SOUNDEX(x), VALUE(x,y,...), and DECODE(x,y(i),...,z)
SELECT * FROM
hotel
WHERE CHR(hno) LIKE '1__'
Displaying the hotel entries whose numbers contain three digits and begin with 1. The hotel number is converted to character values for this purpose.
Result
HNO |
NAME |
ZIP |
ADDRESS |
100 |
Beach |
32018 |
1980 34th St. |
110 |
Atlantik |
33441 |
111 78th Str. |
120 |
Long Beach |
90804 |
35 Broadway |
130 |
Star |
90029 |
13 Beechwood Place |
140 |
River Boat |
70112 |
788 MAIN STREET |
150 |
Indian Horse |
92262 |
16 MAIN STREET |
SELECT * FROM
hotel
WHERE UPPER(name) = 'STAR'
Displaying the hotel name (case insensitive)
Result
HNO |
NAME |
ZIP |
ADDRESS |
130 |
Star |
90029 |
13 Beechwood Place |
SELECT
VALUE(firstname, 'Company') firstname, name
FROM customer
WHERE firstname IS NULL
Displaying the company name; the NULL value is to be replaced by the name Company
Result
FIRSTNAME |
NAME |
Company |
Datasoft |
Company |
Toolware |
SELECT hno,
price, DECODE(type, 'single', 1, 'double', 2, 'suite', 3) code
FROM room
WHERE hno < 60
Displaying the hotel rooms with another name for the room types
Result
HNO |
PRICE |
CODE |
10 |
200 |
2 |
10 |
135 |
1 |
20 |
100 |
2 |
20 |
70 |
1 |
30 |
80 |
2 |
30 |
45 |
1 |
40 |
140 |
2 |
40 |
85 |
1 |
50 |
180 |
2 |
50 |
105 |
1 |
50 |
500 |
3 |