Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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
#plsql, #sql
16 comments
1,887 views
2 mentions