TipoAzul

∮explotación≒ 개발

oracle REGEXP_SUBSTR 예제

파란형 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

댓글

티스토리툴바