Search code examples
sybase

A query to get me specific alphabetic order for sybase

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)

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

In oracle I can do it like this, how do I do it for sybase?

SELECT * FROM user_tab_cols WHERE SUBSTR(UPPER(table_name),1) BETWEEN 'A' and 'Q'
SELECT * FROM user_tab_cols WHERE SUBSTR(UPPER(table_name),1) BETWEEN 'P' and 'Z'

Solution

  • In Sybase you can use the regex (regular expression) to sepecify character ranges [A-G] Assuming your server is set to case insensitive you can do the following:

    SELECT * FROM telephone_contacts WHERE name LIKE "[A-G]%"
SELECT * FROM telephone_contacts WHERE name LIKE "[U-Z]%"

    or

    SELECT * FROM telephone_contacts WHERE name BETWEEN "A%" and "G%"
SELECT * FROM telephone_contacts WHERE name BETWEEN "U%" and "Z%"

    If you find that your server is case sensitive, then you can do what was suggested in another answer, and use upper(name)