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 includeSELECT 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
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.
Was this page helpful?
Glad to hear it! Thank you for your feedback.
Sorry to hear that. Thank you for your feedback.