confusion about using BETWEEN keyword for characters.

General
Enthusiast

confusion about using BETWEEN keyword for characters.

Dear All,

I have found below lines from the manual... can you please explain why 'Stein' is not included in the resultset as mentioned in the NOTE?

SELECT last_name
FROM employee
WHERE last_name BETWEEN 'r' AND 's';

output will be:

last_name
------------
Ryan

Note: 'Stein' is not included because 'S_____' sorts lower than 'Stein'. (Teradata is not case-sensitive by default.)

1 REPLY
Enthusiast

Re: confusion about using BETWEEN keyword for characters.

Last Name will be selected if the last name begins with "r" or the last name is "s" followed by spaces. This is because the 'r' and 's' constants in your between are expanded to the same size as last_name before the compare. So 'Stein' is greater than 's ' (ie 's' packed with spaces).

If you want names beginning with R or S, you have to tell SQL to only check the first character of the name. ie

SELECT last_name
FROM employee
WHERE substring(last_name from 1 for 1) BETWEEN 'r' AND 's';

Lo, and Stein is back!