CODEWITHSUNDEEP
mysqlsqlsql-serverdatabasereplace
Jay2012
Jay2012

Reputation: 9

SQL Replace function will not be working in my case

I have a question regarding to Replace function:

MyTable:

ID     | Header| Year
-----------------------
123    |   1   | 2017
456    |   3   | 2016
658    |   2   | 2015
587    |   6   | 2014
....

I want to replace the income codes with the real values like:

I could not use the Replace function in this case,

SELECT 
    ID, 
    REPLACE (REPLACE(INCOME_CODE, '1','$31230-$41400'), '2', '$41560-$50300'), 
    YEAR 
FROM 
    MyTable

Because the second REPLACE function will grasp any number 2 in the first replacement and replace with $41560-$50300.

It would be came something like $31$41560-$5030030-$41400.

Upvotes: -1

Views: 73

Answers (2)

user7392562
user7392562

Reputation:

You need to use case statement

 SELECT ID
    ,CASE 
        WHEN INCOME_CODE = '1'
            THEN '$31230-$41400' )
        WHEN INCOME_CODE = '2'
            THEN '$41560-$50300'
        END AS income
    ,YEAR
FROM MyTable

Upvotes: 0

Tom
Tom

Reputation: 6663

You could use a CASE statement.

CASE INCOME_CODE
     WHEN 1 THEN '$31230-$41400'
     WHEN 2 THEN '$41560-$50300'
     WHEN 3 THEN '$$51620-$60200'
     WHEN 4 THEN '$61230-$73000'
     ..
END

Upvotes: 0

Related Questions