빅쿼리 정규표현식(Bigquery Regexp)

본 포스팅은 빅쿼리 자주 사용하는 정규표현식을 정리한 문서입니다. 필요하면 여기서 복사해서 쿼리에 붙여넣기!

(*주의) 본 정규식은 정답이 아닙니다. 시간에 따라 그 쓰임이 달라질 수 있기 때문에, 사용자 본인의 목적에 맞추어 수정해서 사용하시면 됩니다.

$Bigquery 정규표현식의 문법 re2

• 문법 설명 : https://github.com/google/re2/wiki/Syntax
• 한글문서 : https://support.google.com/a/answer/1371415?hl=ko
• 참고
  • https://regexr.com/
  • https://www.regextester.com/96683
  • http://beemarketer.co.kr/ga/regex/
  • https://regex101.com/

---

$이메일주소

• 유효한 이메일 주소 참고 : https://c10106.tistory.com/4555

regexp_extract_all('내 이름은 이인영이고, 내 이메일주소는 2innnnn0@kakao.com이다.', '[\\w\\.]+@[\\w\\.]+[A-Za-z]+') as email

예외조건을 추가하면 다음과 같다.
[\\w[:punct:]]+@[\\w[:punct:]]+[A-Za-z]+

$전화번호

• 국내는 010-XXXX-XXXX 통일. (여담으로 020이 나온다는 뉴스를 들었지만, 아직은 010임)

regexp_extract_all('010-1234-5678', '[\\d]{3,4}-[\\d]{3,4}-[\\d]{4}') as phone

$차량번호

• 기본은 00가0000(숫자 2자리+한글+숫자 4자리) 형태지만, 000가0000 처럼 2019-09 기준으로 앞자리가 3자리인 번호판이 등장했다.
• (참고:현대자동차 블로그) 번호판 깨알상식 정리 : https://post.naver.com/viewer/postView.nhn?volumeNo=24171702&memberNo=35787715

regexp_extract_all('차랑번호: 12하1234, 123하1234, 과태료 5만원 부과대상입니다.' , '([0-9]{2,3}[가-힣]{1}\\s{0,1}[0-9]{4})') as license_plate,

$URL

• URL에서 추출할 내용은 보통 get방식으로 넘어오는 파라미터값과 path 정도이다.

"https://www.example.com/video/detail?id=001" as url,
regexp_extract_all("https://www.example.com/video/detail?id=001", "//[^/]+([^?#]+)") as path,
regexp_extract_all("https://www.example.com/video/detail?id=001", "id=[^&]*") as id 

$도로명주소

(참고:우아한형제들 테크블로그) https://woowabros.github.io/study/2016/08/16/easy_and_fun_reg_exp.html

regexp_extract_all('서울특별시 광진구 자양동 256-12', '[가-힣]+[시|도]') as region1, #시/도
regexp_extract_all('서울특별시 광진구 자양동 256-12', '\\s[가-힣]+[시|군|구]') as region2, #시/군/구 
regexp_extract_all('서울특별시 광진구 자양동 256-12', '\\s[가-힣]+[읍|면|동|가|리]\\s[\\d]{1,5}[-][\\d]{1,5}') as region3  #읍/동/면/

$구글맵 위경도 추출

'[0-9]{2}[.][0-9]+[,][0-9]{3}[.][0-9]+'

regexp_extract_all('https://www.google.com/maps/place/@37.5305705,127.080649,15.5z/', '[0-9]{2}[.][0-9]+[,][0-9]{3}[.][0-9]+') as geo

정리

WITH
  memo_t AS (
  SELECT
    '''차량번호 : 10호1234
  연락처 : 010-1234-5678
  이름 : 홍길동
  이메일 : 2innnnn0@kakao.com
  주소 : 서울특별시 광진구 자양동 256-12
  URL : https://velog.io/@2innnnn0/Bigquery-GIS-g5yojur6''' AS memo )
SELECT
  REGEXP_EXTRACT_ALL(memo, '[\\w\\.]+@[\\w\\.]+[A-Za-z]+') AS email,
  REGEXP_EXTRACT_ALL(memo, '[\\d]{3,4}-[\\d]{3,4}-[\\d]{4}') AS phone,
  REGEXP_EXTRACT_ALL(memo, '([0-9]{2,3}[가-힣]{1}\\s{0,1}[0-9]{4})') AS license_plate,
  REGEXP_EXTRACT_ALL(memo, "//[^/]+([^?#]+)") AS url_path,
FROM
  memo_t

(EOD)