Getting wrong result while trying to replace values in columns-CodePudding

SELECT 
    CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Fn and Ln", 
    HIRE_DATE AS "HireDate", 
    COMMISSION_PCT AS "Premium",
    REPLACE('COMMISSION_PCT', '-', '0') 
FROM HR.EMPLOYEES

I have to replace empty values in column COMISSION_PCT with 0, but I get a new column named 'REPLACE('COMMISSION_PCT','-','0')'

With new solution i get this

But it doesnt replace empty values with 0

CodePudding user response：

SELECT 
    CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Fn and Ln", 
    HIRE_DATE AS "HireDate", 
    CASE COMMISSION_PCT 
        WHEN NULL THEN '0'
        WHEN '' THEN '0'
        ELSE COMMISSION_PCT
    END AS "Premium"
FROM HR.EMPLOYEES

It is not clear if by empty you mean that the value is null or it is an empty string, in fact the type of the column is entirely ambiguous... so this solution using a CASE statement covers both possibilities.

For null we could use COALESCE or other provider specific implementations, but you haven't mentioned the type of database that you are using. This is an example if COMMISSION_PCT is a string column type:

SELECT 
    CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Fn and Ln", 
    HIRE_DATE AS "HireDate", 
    COALESCE(COMMISSION_PCT, '0') AS "Premium"
FROM HR.EMPLOYEES

Remove the quotes around the 0 for numeric types

REPLACE is designed to replace all instances of a string within a string with the specified value, so to replace all the - characters with 0, but it requires a non-empty string value to operate on, in your case the value is probably missing altogether.

CodePudding user response：

Try this

SELECT 
    CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Fn and Ln", 
    HIRE_DATE AS "HireDate",
    REPLACE(COMMISSION_PCT, '-', '0') AS "Premium"
FROM HR.EMPLOYEES