Skip to main content
Anonymous
July 21, 2006
Answered

order company list ignore "The"

Hi there,
I need to order a list of companies and contacts. They want me to ignore the "The" that comes before some company name. So they want me to order alphabetically "The Smith Company" by S and not T. How do I do this? Thank you!! I have a regular select statement that pulls up the list. My db is oracle.
    This topic is closed to new replies. Start a new post to keep the conversation going.
    Dan_BracukCorrect answer
    Inspiring
    July 21, 2006
    syntax is db specific of course but this is the general idea. In fact, it should work with oracle unless I messed up (again)

    select company,
    case when lower(substr(company, 1, 4) ) = 'the '
    then substr(company, 5, length(company) - 4)
    else company end sortby
    from etc
    order by sortby
    View reply

    5 replies

    drforbin1970
    Inspiring
    July 21, 2006

    SELECT company_name_examined =
    CASE
    WHEN UPPER(SUBSTRING(company_name_from_table,1,4)) = 'THE ' THEN
    RIGHT(company_name_from_table, LEN(company_name_from_table)-4)
    ELSE
    company_name_from_table
    END
    FROM my_table
    ORDER BY company_name_examined ASC
    Translate
    Dan_BracukCorrect answer
    Inspiring
    July 21, 2006
    syntax is db specific of course but this is the general idea. In fact, it should work with oracle unless I messed up (again)

    select company,
    case when lower(substr(company, 1, 4) ) = 'the '
    then substr(company, 5, length(company) - 4)
    else company end sortby
    from etc
    order by sortby
    Translate
    Anonymous
    July 21, 2006
    I tried to apply your code to my sql statement , and although it did not cause any error, it seemed to have absolutely no affect on my output:
    <cfquery name="Getdata" datasource="#request.dsn#" cachedwithin=#timespan#>
    Select crm_contacts.contact_key, crm_contacts.date_inactive, crm_contacts.first_name,
    crm_contacts.last_name, crm_contacts.prefix, crm_contacts.middle_init, crm_contacts.address1,
    crm_contacts.address2, crm_contacts.city, crm_contacts.state, crm_contacts.country,
    crm_contacts.zip, crm_contacts.title, crm_contacts.service_code, crm_contacts.contact_type,
    crm_contacts.phone, crm_contacts.fax, crm_contacts.e_mail, client_services.service_desc, crm_contacts.company,
    case when lower(substr(crm_contacts.company, 1, 4) ) = 'the '
    then substr(crm_contacts.company, 5, length(crm_contacts.company) - 4)
    else crm_contacts.company end sortby
    from crm_contacts, crm_clients, client_services
    where crm_clients.client_key = crm_contacts.client_key
    AND crm_contacts.service_code = client_services.service_code
    order by service_code, upper(company) ASC
    </cfquery>
    Translate
    Anonymous
    July 21, 2006
    If the number of records returned is not huge, you could place the company names in a list and loop through it, looking for Left(companyList, 4) equaling 'the '. When you find one, delete it. When you are done, do an array sort.
    Translate
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices