How to display name starting from 'z' by using BETWEEN cmd only?

Question

used BETWEEN optr with range 'a' and 'z'.

i want to get all the name by using BETWEEN operator only but as you can see BETWEEN optr only include lower_range(a) and not upper_range(z) , but i also want name starting from character 'z'. can we get it?? note:we can only use BETWEEN optr. You can look into the picture.

Answer 1

As second argument of the BETWEEN, uses the next character after z which is chr(ascii('z')+1) ("{") or use rpad('z',length_of_name_column, 'z').

Answer 2

if you can change also name column :

select * from sample1 where substring(name, 1, 1) between 'a' AND 'z'

if you have many records, using rpad my degrade performance