On this page

    Categories: String

    REGEXP_MATCHES

    Returns true when the specified regular expression matches values in a column. Otherwise, returns false.

    Syntax

    REGEXP_MATCHES(input STRING, regex STRING) → BOOLEAN

    • input: The expression to test.
    • regex: The PCRE regular expression to use for the test.

    Examples

    Lists the store managers whose last names include “oo”.

    SELECT s_manager FROM Samples."samples.dremio.com"."tpcds_sf1000".store WHERE regexp_matches(s_manager, '.*?\Qoo\E.*?') GROUP BY s_manager
    -- Brian Cooke
    -- Richard Brooks
    -- William Moody
    -- Tony Cook
    -- Joseph Crook
    -- Ricky Cooper
    -- Tom Brooks
    -- Barry Booker
    -- Alfred Norwood
    -- Grady Moore
    -- Theo Wood
    -- Walter Hood
    -- Vince Moore
    -- Robert Moore
    -- Cedric Cooper
    -- Hugh Wood
    -- Jorge Dooley
    -- David Wood
    -- Joseph Moore
    -- Kim Bloom
    -- Mario Cook
    -- Peter Woodward
    -- Jason Goode
    -- Nolan Wood
    -- William Coons
    -- Patrick Smoot
    -- John Moody
    -- Jerry Brooks
    -- Jeffery Good
    -- Dominique Cook
    -- Ray Moore
    -- Brandon Moore
    -- Luis Wood
    
    

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

    Usage Notes

    This function is identical to the function REGEXP_LIKE.