Skip to Main Content

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.

How to find repeating sub string

846290Jul 18 2011 — edited Jul 20 2011
Hi,

Needed some help in identifying repeating string.
Below are some examples
 Input String           Output String
   2929                      29
    22                          2
   3737                      37
   231231                  231
Thanks in advance.

Regards
Aks
This post has been answered by Solomon Yakobson on Jul 18 2011
Jump to Answer

Comments

Answer

PlSqlCustom3:

( [node^) function_call

| [node^) datetime_literal

| [node^) function_expression

) & ?node = 'TO_DATE'

& ([arg) expr

| [arg) string_literal

| [arg) pls_expr )

& node^ < arg

& [arg+1) ')'

& [arg-1) '('

;

To work out a problem like this it is better to start with expanded parse tree in the structure panel

datetime_literal_parse_tree.png

Witness the parse tree branches of interest highlighted in orange. Apparently, the parser hasn't been consistent, and in all three TO_DATE calls has been recognized differently. This is why we have to use disjunction. Our first query attempt then is:

AllFunctionCalls:

( [node) function_call

| [node) datetime_literal

| [node) function_expression

) ;

Next, we want only TO_DATE calls which, and the function call name is the child of the node that we just have identified. At this point we could have introduced another attribute, but I have chosen to reidentify the node with TO_DATE, and make the parent to be a function call:

AllToDateCalls:

( [node^) function_call

| [node^) datetime_literal

| [node^) function_expression

) & ?node = 'TO_DATE'

;

Next, we have to introduce the second attribute -- arg, and, again, the grammar payload is different in 3 cases. More important, how to connect these two attributes, the node and the arg? It is ancestor-descendant relation, more specifically the closest ancestor-descendant. It is described at page 12 of

https://vadimtropashko.files.wordpress.com/2019/05/arbori-1.pdf

Actually, since we renamed the node to be the function call name only, it is the parent -- node^ -- which is the ancestor of the arg.

The final condition is that a single argument of the TO_DATE function call would have the open and closed parenthesis as sibling parse tree nodes. The result:

datetime_literal_syntax_colors.png

Marked as Answer by Mike Kutz · Sep 27 2020
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 17 2011
Added on Jul 18 2011
8 comments
9,962 views