How to update or remove only a part of multiple fields in a SQL query

Hello!

I am wondering how to update or remove only a part of multiple fields in a SQL query.

For example, in one of our databases we have postal codes starting with F- and B- followed by the postal code. I want to get rid of the F- and B- in all these fields without removing what's behind these characters.

Regards
Mick

It will help, if you post some sample data (a sample) , with the desired result/output.

For example, I have two companies with the following postal codes:

B-5340
F-55700

I want to remove the B- & F- characters, but leave the rest intact.

By using following query, I see that we have 467 rows with similar postal codes, so I want to do an update query to remove all these characters. Perhaps the query can also give you an idea, so see below.

SELECT ADR_PostCode
FROM Relatie
LEFT OUTER JOIN BedrijfsProfiel ON Relatie.REL_ID = BPR_Relatie
LEFT OUTER JOIN Adres ON Adres.ADR_ID = Relatie.REL_VestigingsAdres
WHERE ADR_PostCode LIKE 'B-%' OR
ADR_PostCode LIKE 'B%' OR
ADR_PostCode LIKE 'F-%' OR
ADR_PostCode LIKE 'F%' OR
ADR_PostCode LIKE 'FR-%' OR
ADR_PostCode LIKE 'L-%' OR
ADR_PostCode LIKE 'L%'

Thanks :slight_smile:

Regards
Mick

One way :

with cte as
( select 'B-5340' as postalAddress UNION all
  select 'F-55700' union all
  select 'A-444'
)

select replace (replace(postalAddress ,'B-',''),'F-','') as newPostalAddress
from cte 

you can add a where clause

where postalAddress like 'B-%'
   or postalAddress like 'F-%'

output:

newPostalAddress
5340
55700
A-444

the same output

select 
    case when substring(postalAddress,1,2) ='B-' 
            or substring(postalAddress,1,2) ='F-'
          then substring(postalAddress,3,len(postalAddress)-2)
          else postalAddress end as newPostalAddress2   
from cte

dbfiddle

1 Like

Thank you, I will try it in a test db first and see if it will give me the desired results.

Cheers

Great!