mariadb instr 함수 커스트마이징 하기 (오라클 버전 파라미터 적용)

mariadb instr 함수 커스트마이징 하기 (오라클 버전 파라미터 적용)

mariadb instr 함수 커스트마이징 하기 (오라클 버전 파라미터 적용)

안녕하세요.
오늘은 mariadb를 사용하면서 불편한 부분 하나를 좀 개선 했습니다.
이미 뭐 아실만한 분들은 그렇게 사용을 하고 계시겠지만~~ 그래도 편하게 사용 하시라고~ instr 함수를 mariadb에서도 오라클과 같이 사용 할 수 있게 만들어 보았습니다.

1. instr 함수

instr 함수는 어떤 문자열 안에서 특정문자가 몇번째에 위치해 있는지 그 위치값을 반환하는 함수이며, 없다면 0을 반환 하도록 되어 있습니다. 한번 볼까요?

select instr('abcabcabcabc','a') from dual;

위와 같이 오라클에서 실행하면 다음과 같이 나오게 됩니다.

instr 결과

당연히 첫번째 위치에 a가 있으니 1이 반환이 되는것을 확인 할 수 있습니다.
위와 같은 형태는 mariadb 에서도 똑같이 사용이 가능 합니다.

#자세한건 아래 링크 한번 확인 해보세요.

https://stricky.tistory.com/210

sql 독학 강의 # 단일행 함수 잘 사용 하기(문자 함수) 4편 -sTricky

2. 오라클에서 instr 함수 파라미터 옵션

오라클에서는 다음과 같이 instr 함수를 사용 할 수 있습니다.

select instr('abcabcabcabc','a',2,3) from dual;

이렇게 하면 주어진 문자열에서 두번째 자리부터 찾는데, 세번째 발견되는 a라는 문자의 위치를 반환 하라는 의미가 됩니다. 이해 가시나요?

요렇게

이렇게 빨간박스에서 처럼 처음 a를 제외한 b부터 (2번째자리) a라는 문자를 찾는데 세번째 있는걸 찾으라는 의미인것 이죠. 그래서 오라클에서 위와 같이 실행을 하면 다음과 같은 결과가 나오게 됩니다.

오라클 instr 결과

이걸 mariadb에서도 한번 해보도록 하겠습니다.
그러면 다음과 같이 error가 발생 하는 것을 알 수 있습니다.

mariadb error

요것을 mariadb에서 에러가 나지 않도록 똑같이 생성을 해주도록 하겠습니다.

 

 

 

3. mariadb 에서 instr 함수 생성 하기

생성하는 스크립트는 다음과 같습니다.
필요하시면 그대로 복사해서 사용하셔도 괜찮습니다.

create function instr(p_text varchar(4000), p_find varchar(4000), p_start int, p_seq int)
    returns int
begin
    declare v_return int default 0;
    declare v_text varchar(4000) default null;
    declare v_temp int default 0;

    declare user_break condition for sqlstate '45000';

    declare exit handler
        for user_break
        BEGIN
            return 0;
            resignal;
        END;

    if p_start = 1 and p_seq = 1 then
        set v_return = instr(p_text, p_find);
        return v_return;

    elseif p_start <= 0 or p_seq <= 0 then
        return -1;

    else
        set v_text = mid(p_text, p_start, 4000);
        while p_seq > 0
            do
                select instr(v_text, p_find)
                into v_return
                from dual;

                if v_return = 0 then
                    signal user_break;
                end if;

                set p_seq = p_seq - 1;
                set v_text = mid(v_text, v_return + 1, 4000);
                set v_temp = v_temp + v_return;

            end while;

        set v_return = v_temp - p_seq + p_start - 1;

        return v_return;

    end if;
end;

사용시 주의 사항은, 오라클 같은 경우에는 파라미터로 0이나 음수가 오면 에러가 나지만, 여기에서는 -1이 출력된다는 점 입니다. 그외 나머지 찾는 문자열이 없을때는 0을 리턴하는등의 내용은 동일 합니다.

아래와 같이 잘 실행이 됩니다.

mariadb 실행 결과

유용하기 잘 사용 하시길 바랍니다. 감사합니다.

2022.01.06 - [Database/mariaDB administrator] - mariadb procedure exception 처리 예제

mariadb procedure exception 처리 예제

2021.11.24 - [Database/mariaDB administrator] - mysql 실무에 유용한 sql 로직 모음 #02

mysql 실무에 유용한 sql 로직 모음 #02

2021.08.13 - [Database/ORACLE] - [oracle] sqlplus로 select 한 결과 csv로 내려받는 방법 spool 사용법

[oracle] sqlplus로 select 한 결과 csv로 내려받는 방법 spool 사용법

by.sTricky