Consultor Eletrônico

Status: Verified

GOAL:

How to Create an SQL-92 VIEW that returns the word after a given string?

GOAL:

How to Write an SQL-92 VIEW using the SUBSTRING function

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.x
OpenEdge 10.x

FIX:

In the following solution, a SELECT * FROM FinalView would return the word immediately after the string "This" from the Sports2000.Customer.Comments field:

1. Create an intermediate view that will make the expressions easier to read and less error prone:

DROP VIEW InitialView;
CREATE VIEW InitialView
(
cComments, iposThis, iposSpace, iLength
)
AS
SELECT
Comments,
LOCATE ( ' ', Comments, ( INSTR ( Comments, 'This' ) ) ) + 1,
LOCATE ( ' ', Comments, LOCATE ( ' ', Comments, ( INSTR ( Comments, 'This' ) )
) + 1),
LOCATE ( ' ', Comments, LOCATE ( ' ', Comments, ( INSTR ( Comments, 'This' ) )
) + 1) - (LOCATE ( ' ', Comments, ( INSTR ( Comments, 'This' ) ) ) + 1)
FROM
pub.Customer
WHERE
INSTR (Comments, 'This') > 0;
COMMIT WORK;

2. Create the final view that would return the desired result on the execution of a SELECT statement:

DROP VIEW FinalView;
CREATE VIEW FinalView
(
cResult
)
AS
SELECT SUBSTRING(cComments, iposThis, iLength) FROM InitialView;
COMMIT WORK;

3. Execute the statement that returns the desired result set; namely the word immediately following the string "This" from the Comments fields that contains "This":

SELECT * FROM InitialView;