∮explotación≒ 개발

oracle REGEXP_SUBSTR 예제

TipoAzul 2023. 5. 1.

REGEXP_SUBSTR 예제

예제) 1
```
REM Extracting letter and number sequences from a string 
```
문자열에서 문자 및 숫자 시퀀스 추출

with strings as ( 
  select 'ABC123' str from dual union all 
  select 'A1B2C3' str from dual union all 
  select '123ABC' str from dual union all 
  select '1A2B3C' str from dual 
) 
  select regexp_substr(str, '[0-9]'), 
         regexp_substr(str, '[0-9].*'),
         regexp_substr(str, '[A-Z][0-9]')
  from   strings

1	123	C1
1	1B2C3	A1
1	123ABC	-
1	1A2B3C	A2

4 행이 선택되었습니다.

REM Extracting passenger names from flight information using REGEXP_SUBSTR

REGEXP_SUBSTR을 사용하여 비행 정보에서 승객 이름 추출

REM Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

문자열 형식은 경로 ( 출발 및 출발 공항 ) / 출발 날짜 ( ddmmyyyy ) / 승객 이름입니다

with strings as ( 
  select 'LHRJFK/010315/SAXONMR' str from dual union all 
  select 'CDGLAX/050515/SMITHMRS' str from dual union all 
  select 'LAXCDG/220515/SMITHMRS' str from dual union all 
  select 'SFOJFK/010615/JONESMISS' str from dual 
) 
  select regexp_substr(str, '[A-Z]{6}'), 
         regexp_substr(str, '[0-9]+'), 
         regexp_substr(str, '[A-Z].*$'), 
         regexp_substr(str, '/[A-Z].*$') 
  from   strings

LHRJFK	010315	LHRJFK / 010315 / SAXONMR	/SAXONMR
CDGLAX	050515	CDGLAX / 050515 / SMITHMRS	/스미스
LAXCDG	220515	LAXCDG / 220515 / SMITHMRS	/스미스
SFOJFK	010615	SFOJFK / 010615 / 존스 미스	/존스 미스

4 행이 선택되었습니다.

REM Extracting letter and number sequences from a string

문자열에서 문자 및 숫자 시퀀스 추출

with strings as ( 
  select 'ABC123' str from dual union all 
  select 'A1B2C3' str from dual union all 
  select '123ABC' str from dual union all 
  select '1A2B3C' str from dual 
) 
  select regexp_substr(str, '[0-9]') first_number, 
         regexp_substr(str, '[0-9].*') first_number_then_everything, 
         regexp_substr(str, '[A-Z][0-9]') first_letter_w_number_after 
  from   strings

ORA-00972 : 식별자가 너무 깁니다

REM Extracting passenger names from flight information using REGEXP_SUBSTR

REGEXP_SUBSTR을 사용하여 비행 정보에서 승객 이름 추출

REM Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

문자열 형식은 경로 ( 출발 및 출발 공항 ) / 출발 날짜 ( ddmmyyyy ) / 승객 이름입니다

with strings as ( 
  select 'LHRJFK/010315/SAXONMR' str from dual union all 
  select 'CDGLAX/050515/SMITHMRS' str from dual union all 
  select 'LAXCDG/220515/SMITHMRS' str from dual union all 
  select 'SFOJFK/010615/JONESMISS' str from dual 
) 
  select regexp_substr(str, '[A-Z]{6}') first_6_letters, 
         regexp_substr(str, '[0-9]+') first_matching_numbers, 
         regexp_substr(str, '[A-Z].*$') first_letter_then_all, 
         regexp_substr(str, '/[A-Z].*$') first_slash_w_letter_after
  from   strings

ORA-00923 : 예상 위치에서 FROM 키워드를 찾을 수 없습니다

REM Extracting letter and number sequences from a string

문자열에서 문자 및 숫자 시퀀스 추출

with strings as ( 
  select 'ABC123' str from dual union all 
  select 'A1B2C3' str from dual union all 
  select '123ABC' str from dual union all 
  select '1A2B3C' str from dual 
) 
  select regexp_substr(str, '[0-9]') first_number, 
         regexp_substr(str, '[0-9].*') first_number_followed_by_all, 
         regexp_substr(str, '[A-Z][0-9]') first_letter_with_num_after 
  from   strings

FIRST_NUMBERFIRST_NUMBER_FOLLOWED_BY_ALLFIRST_LETTER_WITH_NUM_AFTER

1	123	C1
1	1B2C3	A1
1	123ABC	-
1	1A2B3C	A2

4 행이 선택되었습니다.

REM Extracting passenger names from flight information using REGEXP_SUBSTR

REGEXP_SUBSTR을 사용하여 비행 정보에서 승객 이름 추출

REM Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

문자열 형식은 경로 ( 출발 및 출발 공항 ) / 출발 날짜 ( ddmmyyyy ) / 승객 이름입니다

with strings as ( 
  select 'LHRJFK/010315/SAXONMR' str from dual union all 
  select 'CDGLAX/050515/SMITHMRS' str from dual union all 
  select 'LAXCDG/220515/SMITHMRS' str from dual union all 
  select 'SFOJFK/010615/JONESMISS' str from dual 
) 
  select regexp_substr(str, '[A-Z]{6}') first_6_letters, 
         regexp_substr(str, '[0-9]+') first_matching_numbers, 
         regexp_substr(str, '[A-Z].*$') first_letter_then_all, 
         regexp_substr(str, '/[A-Z].*$') first_slash_w_letter_after 
  from   strings

ORA-00972 : 식별자가 너무 깁니다

REM Extracting letter and number sequences from a string

문자열에서 문자 및 숫자 시퀀스 추출

with strings as ( 
  select 'ABC123' str from dual union all 
  select 'A1B2C3' str from dual union all 
  select '123ABC' str from dual union all 
  select '1A2B3C' str from dual 
) 
  select regexp_substr(str, '[0-9]') first_number, 
         regexp_substr(str, '[0-9].*') first_number_followed_by_all, 
         regexp_substr(str, '[A-Z][0-9]') first_letter_with_num_after 
  from   strings

FIRST_NUMBERFIRST_NUMBER_FOLLOWED_BY_ALLFIRST_LETTER_WITH_NUM_AFTER

1	123	C1
1	1B2C3	A1
1	123ABC	-
1	1A2B3C	A2

4 행이 선택되었습니다.

REM Extracting passenger names from flight information using REGEXP_SUBSTR

REGEXP_SUBSTR을 사용하여 비행 정보에서 승객 이름 추출

REM Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

문자열 형식은 경로 ( 출발 및 출발 공항 ) / 출발 날짜 ( ddmmyyyy ) / 승객 이름입니다

with strings as ( 
  select 'LHRJFK/010315/SAXONMR' str from dual union all 
  select 'CDGLAX/050515/SMITHMRS' str from dual union all 
  select 'LAXCDG/220515/SMITHMRS' str from dual union all 
  select 'SFOJFK/010615/JONESMISS' str from dual 
) 
  select regexp_substr(str, '[A-Z]{6}') first_6_letters, 
         regexp_substr(str, '[0-9]+') first_matching_numbers, 
         regexp_substr(str, '[A-Z].*$') first_letter_then_all, 
         regexp_substr(str, '/[A-Z].*$') forward_slash_w_letter_after 
  from   strings

FIRST_6_LETTERSFIRST_MATCHING_NUMBERSFIRST_LETTER_THEN_ALLFORWARD_SLASH_W_LETTER_AFTER

LHRJFK	010315	LHRJFK / 010315 / SAXONMR	/SAXONMR
CDGLAX	050515	CDGLAX / 050515 / SMITHMRS	/SMITHMRS
LAXCDG	220515	LAXCDG / 220515 / SMITHMRS	/SMITHMRS
SFOJFK	010615	SFOJFK / 010615 / JONESMISS	JONESMISS

4 행이 선택되었습니다.

※ 쿠팡 파트너스 활동을 통해 일정액의 수수료를 제공 받을 수 있습니다

저작자표시 비영리 변경금지

'∮explotación≒ 개발' 카테고리의 다른 글

InternalError: too much recursion (0)	2023.04.23
Error: Permission denied to access property (0)	2023.04.23
JavaScript 상속과 프로토타입 (0)	2023.04.17
JavaScript 메모리 관리 (0)	2023.04.17
javascript Array.prototype.indexOf() (0)	2023.04.12

oracle REGEXP_SUBSTR 예제

REGEXP_SUBSTR 예제

'∮explotación≒ 개발' 카테고리의 다른 글

댓글

티스토리툴바