Dremio Cloud
Dremio Software
 
On this page

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