CODEWITHSUNDEEP
mysqlsql
Dejsa Cocan
Dejsa Cocan

Reputation: 1569

Set value of MySQL field when displaying data

I am trying to clean up some MySQL code another programmer put together so that, whenever the state abbreviation is either QC (for Quebec) or ON (for Ontario), the sortOrder is set to 'ZZZZ.'

The MySQL query is pulling from a table with addresses of businesses, and the state field holds the abbreviation of the state or province, and the state_full field holds the full name of the state or province.

This is how the MySQL query looks at the moment:

SELECT DISTINCT 
    state_full, 
    state as sortOrder, 
    '' as state, 
    state as searchState  
FROM vendorlocator 
WHERE LENGTH(zip) < 6 OR ((LENGTH(zip) > 5) AND LOCATE('-',zip) > 0) 
UNION 
   (SELECT DISTINCT 
    state_full, 
    'ZZZZ', 
    state, 
    state as searchState 
        FROM vendorlocator 
        WHERE LENGTH(zip) > 5 AND LOCATE('-', zip) = 0)
ORDER BY sortOrder, state

The way the other programmer set this up looks rather complex and messy, in my opinion..is there an easier way to set the value of sortOrder to 'ZZZZ' for addresses located in Quebec (QC) or Ontario (ON)?

Upvotes: 0

Views: 39

Answers (1)

potashin
potashin

Reputation: 44581

You can use case expression in the select clause and >= instead of separate > and = in the where clause:

select distinct state_full 
     , state as sortOrder 
     , case when (length(zip) > 5 and locate('-',zip) = 0) then 'ZZZZ' else '' as state
     , state as searchState  
from vendorlocator 
where length(zip) < 6 or ((length(zip) > 5) and locate('-',zip) >= 0)

Upvotes: 2

Related Questions