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