SQLTeam.com | Weblogs | Forums

Case statement based on alphabetical order of a code

Transact-SQL
#1

I am writing a stored procedure and in the table I use there are two currency codes per record (Buy Currency and Sell Currency). In the stored procedure I need the results to reflect as Currency 1 and Currency 2 based on alphabetical order of the first letter of the currency code. Currency 1 should always be the currency where the first letter of the currency code is the highest up in the alphabet E.g. if the two currencies on the record is EUR and USD, Currency 1 should reflect as EUR and Currency 2 as USD (as 'E' is higher as 'U').
Another example is for instance where the two currencies are USD and ZAR. In this case Currency 1 should reflect as USD and Currency 2 as ZAR.

Select
Case
When BuyCcy first letter is higher up alphabetically Then BuyCcy
Else SellCcy
End As Currency 1

Any help will be appreciated how to code this alphabetical logic.

#2

You should be able to use the standard logical expressions, for example like shown below. Alphabetical sort will be applied to determine the outcome.
SELECT CASE WHEN BuyCCY > SellCCY THEN BuyCCY ELSE SellCCY END AS Currency1

If the currency codes can be mixed case, you should convert to the same casing before doing the logical comparisons as in
WHEN UPPER(BuyCCY) > UPPER(SellCCY) THEN BuyCCY

#3

hi

i tried to do this ..

hope this helps :slight_smile:

please click arrow to the left for drop create data script 
----------------------
-- create table 

create table data
(
buy_currency varchar(10) , sell_currency varchar(10)
)

go 

---------------------------
-- insert into data 

insert into data select 'USD','ZAR'
insert into data select 'PAK','ARG'
insert into data select 'SAD','CIA'
insert into data select 'LAN','PUK'

go

select    case when buy_currency > sell_currency  then sell_currency else buy_currency  end as currency1    
        , case when buy_currency > sell_currency  then buy_currency  else sell_currency end as currency2
from data

image