Need to replace data in a found string with null

Good afternoon, everyone,

Please forgive me If I am asking a very basic question here. What I am needing to do is update the query below so that when It pulls insurance.insuranceName, it will replace "Non Medicare " with "". The current query is pulling the insurance ID verifying if it is 0 then returning ‘Self Pay’ otherwise it is returning the insID’s name.

This causes some issues with data validation, which I have no control over, where they see the word Medicare and assume that it is a Medicare plan. I would like to remove "Non Medicare" from the returned insurance name and return the rest.

Current Query:
/Pulling Primary Insurance Name and setting any data with a ‘0’ as self-pay/
IF(edi_invoice.PrimaryInsId = 0, ‘Self-Pay’,(SELECT insurance.insuranceName FROM insurance
WHERE insID = edi_invoice.PrimaryInsid AND edi_invoice.PrimaryInsid <> 0)) AS prim_payer_name,

I have tried different placements for the replace command, just not sure where I am needing it to go or if there is a better function for what I am needing to do. This is a data extraction not a situation where I would want the entries changed in the database. just to be clear.

With the current Querry I get the following output

  • MEDICARE – ASC
  • BCBS – ASC
  • UHC Non Medicare Adv UB
  • HEALTHCHOICE

The desired output

  • MEDICARE – ASC
  • BCBS – ASC
  • UHC Adv UB
  • HEALTHCHOICE

>Solution :

Do you mean something like this?

SELECT REPLACE(insurance.insuranceName, 'Non Medicare', '') as insuranceName
FROM insurance 
WHERE insID = edi_invoice.PrimaryInsid 
AND edi_invoice.PrimaryInsid <> 0

Leave a Reply