Below are my DDL and DML Statements
CREATE TABLE a1
(
col1 VARCHAR2 (255 CHAR) NOT NULL,
col2 VARCHAR2 (255 CHAR) NOT NULL
);
INSERT INTO a1 (col1, col2)
VALUES ('I love my pet', 'I love Sachin');
INSERT INTO a1 (col1, col2)
VALUES ('I hate you', 'hate you');
INSERT INTO a1 (col1, col2)
VALUES ('Love me', 'Love you');
Expected Output
| Col1 | Col2 | Word_Match |
|---|
| I love my pet | I love Sachin | 2 |
| I hate you | hate you | 0 |
| Love me | Love you | 1 |
- For first row as in col1 and col2 there are two matched words from beginning of string desired output is 2 ( i.e., I in col1 is matched with I in col2 and love in col1 is matched with love in col2, But my in col1 is not matched with sachin, so comparison should stop and 2 should be returned as output)
- For second row as in col1 and col2 there are no matched words from beginning of string, desired output is 0 (i.e., I in col1 will be compared with hate in col2 as these are not matched, comparison should stop and 0 should be returned)
- For third row as in col1 and col2 there is one matched from beginning of string desired output is 1 (i.e., Love in col1 will be compared with Love in col2, These are matched and comparison continues with second word. Me is compared with you which is not matched so comparison should stop and 1 should be returned
Am looking, if we can acheive this in SQL rather than PLSQL. I have achieved the desired output in PLSQL.
Thanks for your suggestions in advance.