Categories: String
REGEXP_REPLACE
Finds strings that match the given regular expression and replaces the strings with the given string.
Syntax
REGEXP_REPLACE(input STRING, regex STRING, replacement_string STRING) → STRING
- input: The expression to search for a matching string.
- regex: The PCRE regular expression to match against.
- replacement_string: The string with which to replace the matching string.
Examples
Replaces hyphens with " to " in the s_hours column.
SELECT s_store_id, regexp_replace(s_hours, '\Q-\E', ' to ') AS s_hours, s_street_number, s_street_name, s_street_type, s_city, s_state FROM Samples."samples.dremio.com"."tpcds_sf1000".store
-- Raw data
-- AAAAAAAABAAAAAAA, 8AM-4PM, 767, Spring, Wy, Spring Valley, SD
-- AAAAAAAACAAAAAAA, 8AM-4PM, 255, Sycamore, Dr., Buena Vista, MN
-- AAAAAAAACAAAAAAA, 8AM-4PM, 877, Park Laurel, Road, Mount Pleasant, NE
-- AAAAAAAAEAAAAAAA, 8AM-4PM, 27, Lake, Ln, Harmony, AL
-- ...
-- Returned data
-- AAAAAAAABAAAAAAA, 8AM to 4PM, 767, Spring, Wy, Spring Valley, SD
-- AAAAAAAACAAAAAAA, 8AM to 4PM, 255, Sycamore, Dr., Buena Vista, MN
-- AAAAAAAACAAAAAAA, 8AM to 4PM, 877, Park Laurel, Road, Mount Pleasant, NE
-- AAAAAAAAEAAAAAAA, 8AM to 4PM, 27, Lake, Ln, Harmony, AL
-- ...
Replaces abbreviated street types with full-word street types.
SELECT s_store_id, s_street_number, s_street_name,
CASE
WHEN regexp_like(s_street_type, '^\QCir.\E.*?') THEN regexp_replace(s_street_type, '^\QCir.\E.*?', 'Circle')
WHEN regexp_like(s_street_type, '^\QWy\E.*?') THEN regexp_replace(s_street_type, '^\QWy\E', 'Way')
WHEN regexp_like(s_street_type, '^\QDr.\E.*?') THEN regexp_replace(s_street_type, '^\QDr.\E', 'Drive')
WHEN regexp_like(s_street_type, '^\QLn\E.*?') THEN regexp_replace(s_street_type, '^\QLn\E', 'Lane')
WHEN regexp_like(s_street_type, '^\QRD\E.*?') THEN regexp_replace(s_street_type, '^\QRD\E', 'Road')
WHEN regexp_like(s_street_type, '^\QBlvd\E.*?') THEN regexp_replace(s_street_type, '^\QBlvd\E', 'Boulevard')
WHEN regexp_like(s_street_type, '^\QPkwy\E.*?') THEN regexp_replace(s_street_type, '^\QPkwy\E', 'Parkway')
WHEN regexp_like(s_street_type, '^\QST\E.*?') THEN regexp_replace(s_street_type, '^\QST\E', 'Street')
WHEN regexp_like(s_street_type, '^\QCt.\E.*?') THEN regexp_replace(s_street_type, '^\QCt.\E', 'Court')
ELSE s_street_type
END
AS s_street_type,
s_city, s_state, s_zip
FROM Samples."samples.dremio.com"."tpcds_sf1000".store
-- Raw data
-- AAAAAAAABAAAAAAA, 767, Spring , Wy, Spring Valley, SD, 56060
-- AAAAAAAACAAAAAAA, 255, Sycamore , Dr., Buena Vista, MN, 55752
-- AAAAAAAACAAAAAAA, 877, Park Laurel, Road, Mount Pleasant, NE, 61933
-- AAAAAAAAEAAAAAAA, 27, Lake , Ln, Harmony, AL, 35804
-- AAAAAAAAEAAAAAAA, 27, Lee 6th, Court, Union, NC, 28721
-- AAAAAAAAEAAAAAAA, 220, 6th , Lane, Glendale, OH, 43951
-- AAAAAAAAHAAAAAAA, 811, Lee , Circle, Glendale, OH, 43951
-- ...
-- Returned data
-- AAAAAAAABAAAAAAA, 767, Spring, Way, Spring Valley, SD, 56060
-- AAAAAAAACAAAAAAA, 255, Sycamore, Drive, Buena Vista, MN, 55752
-- AAAAAAAACAAAAAAA, 877, Park Laurel, Road, Mount Pleasant, NE, 61933
-- AAAAAAAAEAAAAAAA, 27, Lake, Lane, Harmony, AL, 35804
-- AAAAAAAAEAAAAAAA, 27, Lee 6th, Court, Union, NC, 28721
-- AAAAAAAAEAAAAAAA, 220, 6th, Lane, Glendale, OH, 43951
-- AAAAAAAAHAAAAAAA, 811, Lee, Circle, Glendale, OH, 43951
-- ...
Was this page helpful?
Glad to hear it! Thank you for your feedback.
Sorry to hear that. Thank you for your feedback.