Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Posts
    4

    Unanswered: Equivalent of Oracle's DECODE function on SQLServer

    What is the equivalent function in SQL server for the DECODE function in Oracle?

    For example:
    select decode(customer_type,'B','Business','Non Business')
    from CUSTOMER_TABLE

    This is the function statement that we use currently in Oracle. I need to do this same function in Sql server.

    Thanks for all help.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I'm assuming that customer_type may or may not contain the character 'B'.

    For SQL Server

    Code:
    SELECT CASE customer_type
                     WHEN 'B' THEN 'Business'
                      ELSE 'Non Business'
                END
    FROM CUSTOMER_TABLE 
    
    OR
    
    SELECT CASE WHEN customer_type = 'B' THEN 'Business'
                      ELSE 'Non Business'
                END
    FROM CUSTOMER_TABLE

  3. #3
    Join Date
    Jun 2011
    Posts
    1

    Equivalent of Oracle's DECODE function on SQLServer

    That's good to know. I was wondering the same thing since I've been back in Oracle dabbling with SQL again. I guess my question is whether there is SQL SRVR equivalent function to DECODE or if you have to implement the more verbose option. Any ideas?

    Thanks in advance!

  4. #4
    Join Date
    Nov 2004
    Posts
    1,425
    Provided Answers: 4
    Create a function called dbo.decode with achorozy's solution if you feel typing the CASE statement takes too long.

    Anyway, if you don't know how a CASE construct works, take the (very) short time it takes to learn it. It can be very handy.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •