SQL Query on displaying a result by eliminating on a condition-CodePudding

I am trying to write a SQL query for this sample input and output: here with the following condition: The hierarchy will be the same as below

Remove the records where code = PK and filter is not blank
Update the records having code = PK and filter as blank to 'XXXX'

Can someone help me on this?

I could try only the following code for 1st condition, but this is removing all the records and giving incorrect result:

 select *,
 from table t1
 where code='PK' and length('filter')=0

CodePudding user response：

select key, code, IF(code = 'PK', 'XXXX', filter) as filter
from table_t1
where (
    code != 'PK' 
    OR 
    code = 'PK' AND (filter IS NULL OR filter = '')
)

CodePudding user response：

SELECT   T1.KEY
        ,T1.CODE  
        ,CASE
            WHEN CODE = 'PK' AND COALESCE(FILTER,0) = 0  THEN 'XXXX'
            ELSE T1.FILTER 
         END AS FILTER
FROM TABLE T1
WHERE CODE != 'PK' OR (CODE='PK' AND COALESCE(FILTER,0) = 0)

in your query "length('filter')" returns :6