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.
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:
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.
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%'
One way :
with cte as
( select 'B-5340' as postalAddress UNION all
select 'F-55700' union all
select replace (replace(postalAddress ,'B-',''),'F-','') as newPostalAddress
you can add a where clause
where postalAddress like 'B-%'
or postalAddress like 'F-%'
the same output
case when substring(postalAddress,1,2) ='B-'
or substring(postalAddress,1,2) ='F-'
else postalAddress end as newPostalAddress2
Thank you, I will try it in a test db first and see if it will give me the desired results.