Dremio Cloud
Dremio Software
 
On this page

    Categories: String

    REGEXP_LIKE

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

    Syntax

    REGEXP_LIKE(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_like(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_MATCHES.