Hi Gurus,
Best day is today. Thanks!
I am working in oracle 19c.
I want to compare two strings (csv values) and print the result upto what length characters of strings matched. Also, tell the position point at which strings are differ each other.
(STRING1,STRING2) AS
SELECT 'Vccess,Many,Desktop,MAM,Tap,Partner,WAP,Wireless' AS STRING1,'Vccess,Many,Desktop,MAM,Tap,Partner,WAP,W' AS STRING2 FROM dual
UNION ALL
SELECT 'Vccess,Desktop,External,MAM,Tap,VCD-Endpoint,WAP','Vccess,Desktop,External,MAM,Tap,VCD-Endp' FROM dual
UNION ALL
SELECT 'Vccess,Many,External,MAM,Tap,VCD-Endpoint,WAP,Wireless','Vccess,Many,External,MAM,Tap,VCD-Endpoint' FROM dual
UNION ALL
SELECT 'V ccess,Many,External,MAM,Tap,VCD-Endpoint,WAP,Wireless','Vccess,Many,External,MAM,Tap,VCD-Endpoint' FROM dual
UNION ALL
SELECT 'Vccess,Desktop,External,MAM,Tap,VCD-Endpoint,WAP','Vccess,Desktop,External,MAM,Tap,VCD-Endpoint,WAP' FROM dual
UNION ALL
SELECT ' Vccess,Many,Desktop,MAM,Tap,Partner,WAP,Wireless','Vccess,Many,Desktop,MAM,Tap,Partner,WAP,Wireless' FROM dual;
+-----------------------------------------------------------+--------------------------------------------------+
| STRING1 | STRING2 |
+===========================================================+==================================================+
| Vccess,Many,Desktop,MAM,Tap,Partner,WAP,Wireless | Vccess,Many,Desktop,MAM,Tap,Partner,WAP,W |
+-----------------------------------------------------------+--------------------------------------------------+
| Vccess,Desktop,External,MAM,Tap,VCD-Endpoint,WAP | Vccess,Desktop,External,MAM,Tap,VCD-Endp |
+-----------------------------------------------------------+--------------------------------------------------+
| Vccess,Many,External,MAM,Tap,VCD-Endpoint,WAP,Wireless | Vccess,Many,External,MAM,Tap,VCD-Endpoint |
+-----------------------------------------------------------+--------------------------------------------------+
| V ccess,Many,External,MAM,Tap,VCD-Endpoint,WAP,Wireless | Vccess,Many,External,MAM,Tap,VCD-Endpoint |
+-----------------------------------------------------------+--------------------------------------------------+
| Vccess,Desktop,External,MAM,Tap,VCD-Endpoint,WAP | Vccess,Desktop,External,MAM,Tap,VCD-Endpoint,WAP |
+-----------------------------------------------------------+--------------------------------------------------+
| Vccess,Many,Desktop,MAM,Tap,Partner,WAP,Wireless | Vccess,Many,Desktop,MAM,Tap,Partner,WAP,Wireless |
+-----------------------------------------------------------+--------------------------------------------------+
expected output
+-----------------------------------------------------------+--------------------------------------------------+---------------------+---------------------+----------------------------------------------------+
| STRING1 | STRING2 | Length of String1 | Length of String2 | Difference Position Found in String1 wrt String2 |
+===========================================================+==================================================+=====================+=====================+====================================================+
| Vccess,Many,Desktop,MAM,Tap,Partner,WAP,Wireless | Vccess,Many,Desktop,MAM,Tap,Partner,WAP,W | 48 | 41 | 42 |
+-----------------------------------------------------------+--------------------------------------------------+---------------------+---------------------+----------------------------------------------------+
| Vccess,Desktop,External,MAM,Tap,VCD-Endpoint,WAP | Vccess,Desktop,External,MAM,Tap,VCD-Endp | 48 | 40 | 41 |
+-----------------------------------------------------------+--------------------------------------------------+---------------------+---------------------+----------------------------------------------------+
| Vccess,Many,External,MAM,Tap,VCD-Endpoint,WAP,Wireless | Vccess,Many,External,MAM,Tap,VCD-Endpoint | 54 | 41 | 42 |
+-----------------------------------------------------------+--------------------------------------------------+---------------------+---------------------+----------------------------------------------------+
| V ccess,Many,External,MAM,Tap,VCD-Endpoint,WAP,Wireless | Vccess,Many,External,MAM,Tap,VCD-Endpoint | 55 | 41 | 2 |
+-----------------------------------------------------------+--------------------------------------------------+---------------------+---------------------+----------------------------------------------------+
| Vccess,Desktop,External,MAM,Tap,VCD-Endpoint,WAP | Vccess,Desktop,External,MAM,Tap,VCD-Endpoint,WAP | 48 | 48 | 0 |
+-----------------------------------------------------------+--------------------------------------------------+---------------------+---------------------+----------------------------------------------------+
| Vccess,Many,Desktop,MAM,Tap,Partner,WAP,Wireless | Vccess,Many,Desktop,MAM,Tap,Partner,WAP,Wireless | 49 | 48 | 1 |
+-----------------------------------------------------------+--------------------------------------------------+---------------------+---------------------+----------------------------------------------------+
<img src="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/XADY89EO1AV2/image.png" alt="image.png">
Thanks!