Search code examples
sqljoinreplacesnowflake-cloud-data-platform

SQL find and replace part of a column

I have 2 tables and I was wondering how to replace part of a column in table 1 with a value from table 2 in SQL?

I want to find value "Field1" within Region2 and replace it with "Field2":

Table 1

Table 2

Expected result would look like this:

Region2 Region3
USA USA_L5
CANADA CANADA_L5

It is not only replacing with blanks, as well parts which would be hard to do with regex.

If you have any ideas how this can be achieved please let me know.

Solution

  • Using your example data:

    create table #regions
(
    Region2 varchar(100),
    Region3 varchar(100)
)

create table #lookups
(
    Record int,
    Field1 varchar(100),
    Field2 varchar(100)
)

insert into #regions values
('USA_L6','USA_L5'),
('CANADA_L6','CANADA_L5')

insert into #lookups values
(26,'Central_America','Central America'),
(27,'_L7',null),
(28,'_L6',null),
(29,'_L5',null),
(30,'_L4',null),
(31,'_L3',null),
(32,'_L2',null)

    This query gives the desired output:

    select
    replace(r.Region2,isnull(l.Field1,''),isnull(l.Field2,'')) as Region2,
    r.Region3
from #regions r
left join #lookups l on r.Region2 like '%' + isnull(l.Field1,'') + '%'

    Results:

    Region2 Region3
    USA USA_L5
    CANADA CANADA_L5

    This makes some assumptions about there being a maximum of one match in #lookups.Field1 for each value in #regions.Region2, but your question and examples don't give any indication that this is not the case.