CODEWITHSUNDEEP
oracle-database
Moudiz
Moudiz

Reputation: 7377

A query to get me specific alphabetic order

I have a table called telephone_contacts that contain two columns:

telephone_contacts (
  Name    varchar(100)
  Numbers number(20)
)

the column name contains about 20,000 rows.

I want to filter the name by alphabetic , example:

I want a query that get me only the first 6 alphabetic (A , B, C , D ,E ,F G)

Then, a query that get me the last 6 alphabetic (U,V,W,X,Y,Z)

Edit:

example: the column name contains the following data:
Abe, car, night, range, chicken, zoo, whatsapp,facebook, viber Adu , aramt, Bike, Male, dog,egg
I want a query that get me only (A , B, C , D ,E ,F G) so the results will be
abe ,care ,chicken facebook,adu,aramt,bike, dog, egg
the rest are ignored

Upvotes: 1

Views: 114

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Use BETWEEN if it is sequential.

CHAR version

SELECT * FROM telephone_contacts WHERE SUBSTR(UPPER(Name),1) BETWEEN 'A' and 'G'

And

SELECT * FROM telephone_contacts WHERE SUBSTR(UPPER(Name),1) BETWEEN 'U' and 'Z'

ASCII Version

SELECT * FROM telephone_contacts WHERE ASCII(SUBSTR(UPPER(Name),1)) BETWEEN 65 and 65+6-1

And

SELECT * FROM telephone_contacts WHERE ASCII(SUBSTR(UPPER(Name),1)) BETWEEN 90-6+1 and 90

FOR testing:

WITH my_view AS
  (SELECT chr(level+65-1) my_text FROM dual CONNECT BY level <=26
  )
SELECT * FROM my_view WHERE SUBSTR(UPPER(my_text),1) BETWEEN 'A' AND 'G';

Upvotes: 2

Related Questions