CODEWITHSUNDEEP
sqlgoogle-bigquery
user1367204
user1367204

Reputation: 4797

How to replace only full string rather than substring in BigQuery SQL?

I am using the REPLACE function but that is targeting only substrings rather than the full string. I would like to replace these keys with these values:

key   | value
--------------
'b'   | 'blue'
'bl'  | 'blue'
'BLUE'| 'blue'

and my table looks like this:

color | age 
------------
'b'   | 17
'blue'| 10
'BLUE'| 10

and when I do:

SELECT
color,
age, 
REPLACE(REPLACE(REPLACE(color, 'b', 'blue'), 'bl', 'blue'), 'BLUE', 'blue) as color_cleaned
FROM my_table

I get a table that looks like:

color | age | color_cleaned
---------------------------
'b'   | 17  | 'blue'
'blue'| 10  | 'bluelue'
'BLUE'| 10  | 'bluelue'

How can I make it so that REPLACE only matches on a full string rather than a substring?

Upvotes: 0

Views: 315

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below example for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.map` AS (
  SELECT 'b' key, 'blue' value UNION ALL
  SELECT 'bl', 'blue' UNION ALL
  SELECT 'BLUE', 'blue'
), `project.dataset.my_table` AS (
  SELECT 'b' color, 17 age UNION ALL
  SELECT 'blue' , 10 UNION ALL
  SELECT 'BLUE' , 10 
)
SELECT color, age, value AS color_cleaned
FROM `project.dataset.my_table`
JOIN `project.dataset.map`
ON LOWER(key) = LOWER(color)

with result as 

Row     color   age     color_cleaned    
1       b       17      blue     
2       blue    10      blue     
3       BLUE    10      blue

Upvotes: 1

Related Questions