Skip to Main Content

STRING COMPARISON POSITIONAL Differences

RengudiDec 15 2022
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!
This post has been answered by mathguy on Dec 15 2022
Jump to Answer
Comments
Post Details
Added on Dec 15 2022
16 comments
207 views