Re: Find-and-replace substring in stored procedure
-----Original Message-----
From: Pascal Van Hecke <Pascal.VanHecke@teleatlas.com>
To: informix-list@iiug.org <informix-list@iiug.org>
Date: 29 ''''' 1999 '. 15:38
Subject: Find-and-replace substring in stored procedure
>Does anybody have Find-and-replace stored procedure readily available to
>replace a substring with another one in an lvarchar field?
>Thanks in advance...
>
>Pascal Van Hecke
>TeleAtlas Belgium
>
I use several stored procedures:
Replace - replace a substring with another one;
Stuff - remove a numbers of characters or insert a substring;
Lat - return a first position of substring;
SubStr - return a substring.
----------------------------------------------------------------------------
--
CREATE PROCEDURE Replace ( SourceStr VARCHAR(255),
SearchStr VARCHAR(255),
ReplaceStr VARCHAR(255) DEFAULT '',
SPos SMALLINT DEFAULT 1 )
RETURNING VARCHAR(255);
DEFINE SearchLen SMALLINT;
DEFINE ReplaceLen SMALLINT;
LET SearchLen = CHAR_LENGTH(SearchStr);
LET ReplaceLen = CHAR_LENGTH(ReplaceStr);
LET SPos = LAt(SourceStr, SearchStr, SPos);
WHILE SPos > 0
LET SourceStr = Stuff(SourceStr, SPos, SearchLen, ReplaceStr);
LET SPos = LAt(SourceStr, SearchStr, SPos + ReplaceLen);
END WHILE
RETURN SourceStr;
END PROCEDURE;
----------------------------------------------------------------------------
--
CREATE PROCEDURE Stuff ( InStr VARCHAR(255),
SPos SMALLINT,
DelLen SMALLINT,
InsStr VARCHAR(255) DEFAULT '' )
RETURNING VARCHAR(255);
DEFINE OutStr VARCHAR(255);
DEFINE i SMALLINT;
DEFINE RestLen SMALLINT;
IF InStr IS NOT NULL AND (SPos > 0) THEN
IF SPos = 1 THEN
LET OutStr = '';
ELSE
LET OutStr = SubStr(InStr, 1, SPos - 1);
END IF
IF InsStr > '' THEN
IF SPos - 1 + CHAR_LENGTH(InsStr) > 255 THEN
LET OutStr = OutStr||SubStr(InsStr, 1, 255 - (SPos - 1));
ELSE
LET OutStr = OutStr||InsStr;
END IF
END IF
LET RestLen = CHAR_LENGTH(InStr) - SPos + 1;
IF DelLen > 0 THEN
IF RestLen < DelLen THEN
LET SPos = SPos + RestLen;
ELSE
LET SPos = SPos + DelLen;
END IF
END IF
IF CHAR_LENGTH(OutStr) + RestLen > 255 THEN
LET OutStr = OutStr||SubStr(InStr, SPos, 255 - CHAR_LENGTH(OutStr));
ELSE
LET OutStr = OutStr||SubStr(InStr, SPos, RestLen);
END IF
ELSE
LET OutStr = NULL;
END IF
RETURN OutStr;
END PROCEDURE;
----------------------------------------------------------------------------
--
CREATE PROCEDURE LAt ( SourceStr VARCHAR(255),
SearchStr VARCHAR(255),
SPos SMALLINT DEFAULT 1 )
RETURNING SMALLINT;
DEFINE FPos SMALLINT;
DEFINE SourceLen SMALLINT;
DEFINE SearchLen SMALLINT;
LET FPos = 0;
IF SourceStr IS NOT NULL AND SearchStr IS NOT NULL THEN
LET SourceLen = CHAR_LENGTH(SourceStr);
LET SearchLen = CHAR_LENGTH(SearchStr);
IF (SPos BETWEEN 1 AND SourceLen) AND
(SearchLen > 0) AND
(SPos - 1 + SearchLen <= SourceLen)
THEN
FOR SPos = SPos TO (SPos + SourceLen - SearchLen)
IF SearchStr = SubStr(SourceStr, SPos, SearchLen) THEN
LET FPos = SPos;
EXIT FOR;
END IF
END FOR
END IF
END IF
RETURN FPos;
END PROCEDURE;
----------------------------------------------------------------------------
--
CREATE PROCEDURE SubStr ( InStr VARCHAR(255),
SPos SMALLINT,
OutLen SMALLINT DEFAULT 1 )
RETURNING VARCHAR(255);
DEFINE OutStr VARCHAR(255);
DEFINE i SMALLINT;
DEFINE StrLen SMALLINT;
IF InStr IS NOT NULL THEN
LET OutStr = '';
IF OutLen > 0 THEN
LET StrLen = CHAR_LENGTH(InStr);
IF SPos BETWEEN 1 AND StrLen THEN
IF SPos > 1 THEN
FOR i = 2 TO SPos
LET InStr = InStr[2,255];
END FOR
END IF
IF OutLen > (StrLen - SPos + 1) THEN
LET OutLen = StrLen - SPos + 1;
END IF
FOR i = 1 TO OutLen
LET OutStr = OutStr||InStr[1,1];
LET InStr = InStr[2,255];
END FOR
END IF
END IF
ELSE
LET OutStr = NULL;
END IF
RETURN OutStr;
END PROCEDURE;
----------------------------------------------------------------------------
--
Remember, that in Informix version 7.3X exists functions REPLACE and SUBSTR.
Best regards,
Danail.