Consultor Eletrônico

Status: Unverified

GOAL:

A STORED PROCEDURE to extract the word after a given word from a given field.

FIX:

The following stored procedure extracts the first word after the word 'is' from the comments field of the customer table in the Progress Sports2000 demo database:

DROP PROCEDURE ExtractAfterIs;
CREATE PROCEDURE ExtractAfterIs()

RESULT (
cWord CHARACTER (25)
)
BEGIN
SQLIStatement dropTableMaxime = new SQLIStatement ( "DROP TABLE Maxime" );
dropTableMaxime.execute();

SQLIStatement createTableMaxime = new SQLIStatement ("CREATE TABLE maxime (cComments CHAR (160), iStartWord INTEGER, iEndWord INTEGER, iLengthWord INTEGER, cWord CHAR (20) ) ");
createTableMaxime.execute();

SQLIStatement insertInoMaxime = new SQLIStatement("INSERT INTO Maxime ( cComments ) SELECT Comments FROM pub.Customer WHERE INSTR (Comments, ' is ') > 0");
insertInoMaxime.execute();

SQLIStatement updateiStartWord = new SQLIStatement("UPDATE Maxime SET iStartWord = INSTR (cComments, ' is ') + 4 WHERE INSTR(cComments, ' is ') > 0");
updateiStartWord.execute();

SQLIStatement updateEndWord = new SQLIStatement("UPDATE Maxime SET iEndWord = LOCATE ( ' ', cComments, iStartWord) - 1");
updateEndWord.execute();

SQLIStatement updateiLengthWord = new SQLIStatement("UPDATE Maxime SET iLengthWord = iEndWord - iStartWord + 1");
updateiLengthWord.execute();

SQLIStatement updatecWord = new SQLIStatement("UPDATE Maxime SET cWord = SUBSTRING(cComments, iStartWord, iLengthWord)");
updatecWord.execute();

SQLCursor maximeCursor = new SQLCursor("SELECT cWord FROM Maxime");
maximeCursor.open();

do {
maximeCursor.fetch();
if (maximeCursor.found()) {
String cWord = (String) maximeCursor.getValue(1, CHARACTER);
SQLResultSet.set (1, cWord);
SQLResultSet.insert ();
}
} while ( maximeCursor.found() );
maximeCursor.close ();
END
COMMIT WORK;