database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Tutorials 4285 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 4285
mimic oracle's replace function. versions in pltcl and plpgsql
작성자
정재익(advance)
작성일
2002-08-05 22:59
조회수
4,389

원본출처 : http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=23

 

Submitted on: 03-16-2001

Description:

mimic oracle's replace function. versions in pltcl and plpgsql.

Code:

-- by Jonathan Ellis (jbellis@hotmail.com)
-- licensed under the GPL
-- emailing me improvements is appreciated but not required

-- args: string substring replacement_substring
create function replace (varchar, varchar, varchar) returns varchar as '
    -- escape out characters that regsub would treat as special
    regsub -all {&} "$3" {\\\&} 3
    regsub -all {\\[0-9]} "$3" {\\\0} 3
    eval "regsub -all \{$2\} \{$1\} \{$3\} rval"
    return $rval
' language 'pltcl';

-- plpgsql version so we don't have to jump through hoops to call it from other functions

create function replace (varchar, varchar, varchar) returns varchar as '
declare
    string alias for $1;
    sub alias for $2;
    replacement alias for $3;
    -- xxxxxxxxxxx[MATCH]xxxxxxxxxxxx
    --           | end_before
    --                   | start_after
    match integer;
    end_before integer;
    start_after integer;
    string_replaced varchar;
    string_remainder varchar;
begin
    string_remainder := string;
    string_replaced := '''';
    match := position(sub in string_remainder);

    while match > 0 loop
        end_before := match - 1;
        start_after := match + length(sub);
        string_replaced := string_replaced || substr(string_remainder, 1, end_b
efore) || replacement;
        string_remainder := substr(string_remainder, start_after);
        match := position(sub in string_remainder);
    end loop;
    string_replaced := string_replaced || string_remainder;

    return string_replaced;
end;
' LANGUAGE 'plpgsql';
[Top]
No.
제목
작성자
작성일
조회
4309Global Transactions - X/Open XA - Resource Managers
정재익
2002-08-23
5891
4301Migrating from MySQL to PostgreSQL
정재익
2002-08-15
5698
4300The PostgreSQL JDBC Primer
정재익
2002-08-15
5688
4285mimic oracle's replace function. versions in pltcl and plpgsql
정재익
2002-08-05
4389
4275LAMPS Tutorial v2.0
정재익
2002-08-01
6577
4274Tutorial - Installation of PostgreSQL, Apache and PHP
정재익
2002-08-01
6150
4272PostgreSQL에서 집합 함수와 연산자 사용하기
정재익
2002-07-29
5939
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2020 DSN, All rights reserved.
작업시간: 0.010초, 이곳 서비스는
	PostgreSQL v13.0으로 자료를 관리합니다