본문 바로가기
통계프로그램 비교 시리즈/오라클함수 비교(R & Python)

REGEXP_INSTR 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]

by 기서무나구물 2021. 12. 13.

포스팅 목차

    * 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크


    [ REGEXP_INSTR Oracle Function ]

     


    REGEXP_INSTR 함수는 입력한 문자열에서 정규 표현식 패턴을 검색하여서 지정한 패턴(정규 표현식)과 일치하는 부분의 최초의 위치(무슨 문자인지)를 반환한다. 또한, 패턴 검색을 시작하기 위한 시작 위치를 지정하는 것도 가능합니다. REGEXP_INSTR함수는 정규 표현 패턴에 대한 문자열을 검색하는 INSTR 함수의 기능을 확장한 것이다. 이 함수는 입력 문자 세트에 의해 정의되는 것 과 같은 문자를 이용하여 문자열을 평가한다. 그것은 return_option인수의 값에 의존하는 일치되는 substring의 처음과 종료 위치를 나타내는 정수를 반환한다. 만약 일치하는 값이 발견되지 않으면, 함수는 0을 반환한다.

     

     

     

     


    1. Oracle(오라클)

     

    REGEXP_INSTR() 함수

    문자열(‘abcabcabc’)의 2 번째 문자열을 시작으로 사용자가 지정한 패턴(“abc”)이 조회되는 첫 번째 위치를 반환한다.

    Oracle Programming
    SELECT REGEXP_INSTR('abcabcabc','abc', 2) REGEXP_INSTR_FUNC
    FROM   DUAL

     

    Results
    REGEXP_INSTR_FUNC
    -----------------------
    4

     


    2. Python Pandas(파이썬)

     

    re.finditer() 함수

    문자열(‘abcabcabc’)의 2 번째 문자열을 시작으로 사용자가 지정한 패턴(“abc”)이 조회되는 첫 번째 위치를 반환한다.

    Python Programming
    import re
    
    # word = 'abcabcabc'
    # pattern = re.compile(r'abc')
    # starts = [match.start() for match in pattern.finditer(word) if match.start() > 1][0]
    # print(starts)
    
    [match.start() for match in re.compile(r'abc').finditer('abcabcabc') if match.start() > 1][0]

     

    Results
    3

     


    re.finditer() 함수

    1차적으로 문자열(‘abcabcabc’)을 검색하여서 사용자가 지정한 패턴(“abc”)의 시작 위치와 종료 위치를 확인한다. 기준 문자열(‘abcabcabc’)에는 지정한 패턴* (“abc”)이 2곳에서 확인된다.


    - [0][0][0] : 인덱스를 사용하여서 첫 번째로 검색되는 위치의 시작 위치를 조회한다.

    Python Programming
    str_DF = pd.DataFrame(['abcabcabc'],columns=['str'])
    
    str_DF.str.apply(lambda x:[(m.start(),m.end()) for m in re.finditer('A',x,flags=re.I) if m.start() > 1])[0][0][0]

     

    Results
    3

     


    re.finditer() 함수

    - EMP 참고 예제 : 직무(Job)의 문자열에서 2번째 위치를 시작으로 문자 ‘E’가 검색되는 시작 위치와 종료 위치를 리스트로 반환한다.

    Python Programming
    import re
    
    emp.job.apply(lambda x:[(m.start(),m.end())  for m in re.finditer('E',x,flags=re.I) if m.start() > 1])

     

    Results
    0             [(2, 3)]
    1             [(3, 4)]
    2             [(3, 4)]
    3             [(5, 6)]
    4             [(3, 4)]
    5             [(5, 6)]
    6             [(5, 6)]
    7                   []
    8     [(2, 3), (6, 7)]
    9             [(3, 4)]
    10            [(2, 3)]
    11            [(2, 3)]
    12                  []
    13            [(2, 3)]
    Name: job, dtype: object

     


    [참고] re.finditer

    • start() : 검색 패턴과 일치하는 문자열의 시작 위치
    • end() : 검색 패턴과 일치하는 문자열의 종료 위치
    • span() : 패턴이 검색되는 위치에 대한 정보를 (시작위치, 종료 위치)과 같은 튜플 형식으로 반환한다.
    • group() : 검색하는 문자열 정보를 반환한다.
    Python Programming
    expr  = 'abc'
    str   = 'abcabcacbacabcabc'
    itrobj=re.finditer(expr,str)
    display(itrobj)
    
    [(item.start(),item.end(),item.span(),item.group()) for item in itrobj]

     

    Results
    <callable_iterator at 0x1300ec1c580>
    
    
    
    
    
    [(0, 3, (0, 3), 'abc'),
     (3, 6, (3, 6), 'abc'),
     (11, 14, (11, 14), 'abc'),
     (14, 17, (14, 17), 'abc')]

     


    참고 : np.searchsorted

    문자열을 공백으로 분리하여 공백의 위치를 저장 후, 사용자가 지정한 인덱스에 해당하는 문자열을 반환한다.

    Python Programming
    s =  "통계 분석 연구회 statwith 데이터 분석"
    
    words = s.split()
    
    display(words)
    
    ix_start_word = [i+1 for i,s in enumerate(s) if s==' ']
    
    display(ix_start_word)
    
    words[np.searchsorted(ix_start_word, 4)] # 4번째 인덱스에 위치한 '분석' 을 반환한다.

     

    Results
    ['통계', '분석', '연구회', 'statwith', '데이터', '분석']
    
    
    
    [3, 6, 10, 19, 23]
    
    
    
    
    
    '분석'

     

     


    3. R Programming (R Package)

     

    사용자 정의 함수 생성

    문자열에서 2번째 위치 이후에 ‘abc’ 문자열이 조회되는 첫 번째 위치를 반환한다.

     

    - 출처 : https://stackoverflow.com/questions/14249562/find-the-location-of-a-character-in-string/32804318#32804318

    R Programming
    %%R
    
    instr <- function(str1,str2,startpos=2,n=1){
        aa = unlist(strsplit(substring(str1,startpos),str2))
        if(length(aa) < n+1 ) return(0);
        return(sum(nchar(aa[1:n])) + startpos+(n-1)*nchar(str2) )
    }
    
    
    instr('abcabcabc','abc',2,1)

     

    Results
    [1] 4

     


    stringr::str_locate_all 함수

    stringr::str_locate_all 함수는 문자열(‘abcabcabc’)을 검색하여서 사용자가 지정한 패턴(“abc”)의 시작 위치와 종료 위치를 반환한다.

    R Programming
    %%R
    
    stringr::str_locate_all('abcabcabc', "abc")

     

    Results
    [[1]]
         start end
    [1,]     1   3
    [2,]     4   6
    [3,]     7   9

     


    str_locate_all 함수

    문자열을 검색하여서 사용자가 지정한 패턴의 시작 위치와 종료 위치로 구성된 리스트를 작성한다. 리스트에서 2번째 위치에 해당하는 인덱스를 조회 후 이를 제외한 후 검색되는 행에서 시작 위치(start)를 반환한다.

    R Programming
    %%R
    
    with( as.data.frame(str_locate_all("abcabcabc", 'abc')), start[ ! (start <= 2  &  2 <= end) ][1] )

     

    Results
    [1] 4

     


    str_locate_all 함수

    R Programming
    %%R
    x <- c("abcabcabc")
    
    as.data.frame(str_locate_all(x, 'abc')) [ sapply(str_locate_all(x, 'abc'), function(x) !(x[,'start'] <= 2 & x[,'end'] >= 2 )) , ][1,'start']

     

    Results
    [1] 4

     


    stringr::str_locate() 함수

    R Programming
    %%R
    
    stringr::str_locate(str_sub('abcabcabc',2), "abc")[,'start'] + 1

     

    Results
    start 
        4 

     


    gregexpr 함수

    gregexpr 함수를 사용하여서 text(‘abcabcabc’) 문자열에서 패턴(‘abc’)과 일치하는 문자열을 검색하여 일치하는 모든 문자열의 위치 정보를 리스트 형태로 반환한다. 위치 정보를 포함하고 있는 리스트를 사용하여서 2번째 위치 이후에 발견된 패턴의 시작 위치를 반환한다.

    R Programming
    %%R
    
    positions_a= gregexpr(pattern = "abc", text =  'abcabcabc', ignore.case = TRUE)
    
    print(positions_a)
    
    sapply(positions_a, function(x) x[which(x >2)][1] )

     

    Results
    [[1]]
    [1] 1 4 7
    attr(,"match.length")
    [1] 3 3 3
    attr(,"index.type")
    [1] "chars"
    attr(,"useBytes")
    [1] TRUE
    
    [1] 4

     


    gregexpr 함수

    R Programming
    %%R
    print(gregexpr(pattern = 'abc', 'abcabcabc')[1])
    
    lapply(gregexpr(pattern = 'abc', 'abcabcabc'), function(x) { dplyr::nth ( x[which(x >2)] , 1) })

     

    Results
    [[1]]
    [1] 1 4 7
    attr(,"match.length")
    [1] 3 3 3
    attr(,"index.type")
    [1] "chars"
    attr(,"useBytes")
    [1] TRUE
    
    [[1]]
    [1] 4

     


    [ 참고 ]

    • grep 함수 : 벡터에서 개별 문자열을 검색하여서 지정한 패턴이 존재하는 벡터 인덱스 번호를 반환한다.
    • gregexpr 함수 : 문자열을 검색하여서 지정한 패턴과 일치하는 모든 문자의 위치 정보(위치, 길이, 타입 등)를 반환한다.
    R Programming
    %%R
    
    grep('abc', c('back','bcabcabc','abc','back'))

     

    Results
    [1] 2 3

     

    • regexpr() 함수와 gregexpr() 함수
    R Programming
    %%R
    
    regexpr('abc', 'abcabcabc') # 최초 위치 정보를 반환
    gregexpr('abc', 'abcabcabc')

     

    Results
    [[1]]
    [1] 1 4 7
    attr(,"match.length")
    [1] 3 3 3
    attr(,"index.type")
    [1] "chars"
    attr(,"useBytes")
    [1] TRUE

     


    참고 : stringi 패키지

    • stringi::stri_locate_last_fixed : 문자열을 검색하여서 패턴과 일치하는 마지막 문자의 위치 정보(시작 위치, 종료 위치)를 반환한다.
    R Programming
    %%R
    x <- "abcabcabc"
    stringi::stri_locate_last_fixed(x, "abc")

     

    Results
         start end
    [1,]     7   9

     

     


    4. R Dplyr Package

     

    str_c 함수 : 인수로 지정한 문자열을 결합한다.

    사원 이름과 직무를 결합한 문자열을 검색하여서 패턴(“LE”)과 일치하는 첫번재와 두 번째 문자의 시작 위치와 종료 위치를 반환한다.

    R Programming
    %%R
    
    emp %>%
      dplyr::rowwise() %>% 
      dplyr::mutate(concat_str  =  stringr::str_c(ename,job),
                    start_pos_1 =  str_locate_all(stringr::str_c(ename,job), 'LE')[[1]][,1][1],
                    end_pos_1   =  str_locate_all(stringr::str_c(ename,job), 'LE')[[1]][,2][1],
                    start_pos_2 =  str_locate_all(stringr::str_c(ename,job), 'LE')[[1]][,1][2],
                    end_pos_2   =  str_locate_all(stringr::str_c(ename,job), 'LE')[[1]][,2][2]) %>%
      dplyr::select( -c(mgr,sal,hiredate,deptno,comm) ) %>%
      head()

     

    Results
    # A tibble: 6 x 8
    # Rowwise: 
      empno ename  job     concat_str    start_pos_1 end_pos_1 start_pos_2 end_pos_2
      <dbl> <chr>  <chr>   <chr>               <int>     <int>       <int>     <int>
    1  7369 SMITH  CLERK   SMITHCLERK              7         8          NA        NA
    2  7499 ALLEN  SALESM~ ALLENSALESMAN           3         4           8         9
    3  7521 WARD   SALESM~ WARDSALESMAN            7         8          NA        NA
    4  7566 JONES  MANAGER JONESMANAGER           NA        NA          NA        NA
    5  7654 MARTIN SALESM~ MARTINSALESM~           9        10          NA        NA
    6  7698 BLAKE  MANAGER BLAKEMANAGER           NA        NA          NA        NA

     


    str_locate_all() 함수

    R Programming
    %%R
    
    emp %>%
      dplyr::rowwise() %>% 
      dplyr::mutate(pos = list( data.frame( str_locate_all(job, 'E') ) )) %>% 
      unnest(pos)

     

    Results
    # A tibble: 13 x 10
       empno ename  job         mgr hiredate     sal  comm deptno start   end
       <dbl> <chr>  <chr>     <dbl> <date>     <dbl> <dbl>  <dbl> <int> <int>
     1  7369 SMITH  CLERK      7902 1980-12-17   800    NA     20     3     3
     2  7499 ALLEN  SALESMAN   7698 1981-02-20  1600   300     30     4     4
     3  7521 WARD   SALESMAN   7698 1981-02-22  1250   500     30     4     4
     4  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20     6     6
     5  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30     4     4
     6  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30     6     6
     7  7782 CLARK  MANAGER    7839 1981-01-09  2450    NA     10     6     6
     8  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10     3     3
     9  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10     7     7
    10  7844 TURNER SALESMAN   7698 1981-09-08  1500     0     30     4     4
    11  7876 ADAMS  CLERK      7788 1983-01-12  1100    NA     20     3     3
    12  7900 JAMES  CLERK      7698 1981-12-03   950    NA     30     3     3
    13  7934 MILLER CLERK      7782 1982-01-23  1300    NA     10     3     3

     


    5. R sqldf Package

     

    instr 함수

    문자열에서 2번째 위치 이후에 ‘abc’ 문자열이 조회되는 첫 번째 문자의 위치를 반환한다.

     

    - https://stackoverflow.com/questions/46968230/applying-instr-function-in-reverse-order-using-sqldf

    R Programming
    %%R
    library(RH2)
    
    sqldf(" SELECT instr('abcabcabc', 'abc',2) REGEXP_INSTR ")

     

    Results
      REGEXP_INSTR
    1            4

     


    6. Python pandasql Package

     

    instr 함수

    Python Programming
    ps.sqldf(" SELECT instr('abcabcabc', 'abc') REGEXP_INSTR ")

     

    Results
    	REGEXP_INSTR
    0	1

     


    7. R data.table Package

     

    str_c 함수 : 인수로 지정한 문자열을 결합한다.

    사원 이름과 직무를 결합한 문자열을 검색하여서 패턴(“LE”)과 일치하는 첫번재와 두 번째 문자의 시작 위치와 종료 위치를 반환한다.

    R Programming
    %%R
    
    DT          <- data.table(emp)
    dept_DT     <- data.table(dept)
    
    DT[, `:=`(concat_str  =  stringr::str_c(ename,job),
              start_pos_1 =  str_locate_all(stringr::str_c(ename,job), 'LE')[[1]][,1][1],
              end_pos_1   =  str_locate_all(stringr::str_c(ename,job), 'LE')[[1]][,2][1],
              start_pos_2 =  str_locate_all(stringr::str_c(ename,job), 'LE')[[1]][,1][2],
              end_pos_2   =  str_locate_all(stringr::str_c(ename,job), 'LE')[[1]][,2][2]), by = 1:nrow(DT)][,c("comm","mgr","empno","hiredate","sal","deptno") := NULL]

     

    Results
         ename       job     concat_str start_pos_1 end_pos_1 start_pos_2 end_pos_2
     1:  SMITH     CLERK     SMITHCLERK           7         8          NA        NA
     2:  ALLEN  SALESMAN  ALLENSALESMAN           3         4           8         9
     3:   WARD  SALESMAN   WARDSALESMAN           7         8          NA        NA
     4:  JONES   MANAGER   JONESMANAGER          NA        NA          NA        NA
     5: MARTIN  SALESMAN MARTINSALESMAN           9        10          NA        NA
     6:  BLAKE   MANAGER   BLAKEMANAGER          NA        NA          NA        NA
     7:  CLARK   MANAGER   CLARKMANAGER          NA        NA          NA        NA
     8:  SCOTT   ANALYST   SCOTTANALYST          NA        NA          NA        NA
     9:   KING PRESIDENT  KINGPRESIDENT          NA        NA          NA        NA
    10: TURNER  SALESMAN TURNERSALESMAN           9        10          NA        NA
    11:  ADAMS     CLERK     ADAMSCLERK           7         8          NA        NA
    12:  JAMES     CLERK     JAMESCLERK           7         8          NA        NA
    13:   FORD   ANALYST    FORDANALYST          NA        NA          NA        NA
    14: MILLER     CLERK    MILLERCLERK           4         5           8         9

     


    8. Python Duckdb의 SQL

     

    Python Programming
    %%sql
      SELECT instr('abcabcabc', 'abc')  instr_1,
             strpos('abcabcabc', 'abc') instr_2

     

    Python Programming
    duckdb.sql(" SELECT instr('abcabcabc', 'abc')  instr_1,            \
                        strpos('abcabcabc', 'abc') instr_2 ").df()

     

    Results
       instr_1  instr_2
    0        1        1

     

     


    ( https://unsplash.com/photos/afW1hht0NSs )

      --------------------------------------------  

     

     

    [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] 오라클 함수와 R & Python 비교 사전 목록 링크

     

    오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

     

    [SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크
    반응형

    댓글