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.
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%'
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