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!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
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
Added on Dec 15 2022
#plsql, #sql
16 comments
1,320 views
2 mentions
SQL & PL/SQL

STRING COMPARISON POSITIONAL Differences

Comments

Post Details
