Conditionally strip last few characters

[mightycpa]

Hi,

I've got this working, but I'm wondering if there is a more elegant, simpler way to do it.

I have a script that gathers table names from SQL scripts. Sometimes, my script will pick up a fully qualified column name, and so I want to strip off the column name, leaving just the table name. For my tables and columns, the naming convention looks like this:

SCHEMA.OWNER.SOME_TABLE_NAME.SOME_COLUMN

Given that, I would want to yield this:

SCHEMA.OWNER.SOME_TABLE_NAME

Here's how I've done that:

I notice that in my naming convention, if a period follows an underscore, then that period marks the beginning of a column name. I parse through the string in order to find that condition. When I do, I SEPARATE the string using a period as my delimiter, then I just put all but the last item in the array back together, then strip off the final period, like so:

Code: Select all

Let>myvar=SCHEMA.OWNER.SOME_TABLE_NAME.SOME_COLUMN
Let>newstr=
RegEx>[^a-zA-Z0-9 -],myvar,0,m,nm,1,,mynewvar
Repeat>nm
  If>m_%nm%=.
    Let>v_pd_flag=1
  Endif
  If>m_%nm%=_
    If>v_pd_flag=1
      Separate>myvar,.,v_parts
      Let>j=1
      Repeat>j
        Concat>newstr,v_parts_%j%
        Concat>newstr,.
        Add>j,1
      Until>j=v_parts_count
      Let>nm=1
    EndIf
  EndIf
  Add>nm,-1
Until>nm<2

Let>newstr2={copy(%newstr%,1,length(%newstr%)-1)}
MessageModal>newstr2

But that sure does seem to be a lot of code, and I just wonder if there is a better way. Any ideas anybody?

Thanks in advance.

[Marcus Tettmar]

Try this cunning method:

Code: Select all

Let>str=schema_owner.some_table_name.some_column
ExtractFileExt>str,last_bit
StringReplace>str,last_bit,,str

[mightycpa]

That's really nice Marcus. Cunning indeed!

My headline may have been a little misleading, there are actually two parts to my code:

1) Does the string contain a column name or not?

2) If so, strip off the last few characters.

So, to do the whole shooting match:

Code: Select all

Let>myvar=SCHEMA.OWNER.SOME_TABLE_NAME.SOME_COLUMN
RegEx>[^a-zA-Z0-9 -],myvar,0,m,nm,1,,mynewvar
Repeat>nm
  If>m_%nm%=.
    Let>v_pd_flag=1
  Endif
  If>m_%nm%=_
    If>v_pd_flag=1   //if a period exists to the right of an underscore
       ExtractFileExt>myvar,last_bit
       StringReplace>myvar,last_bit,,myvar
       Let>nm=1
    EndIf
  Endif
  Add>nm,-1
Until>nm<2
MessageModal>myvar

Two questions: Is there any danger in doing this?

StringReplace>myvar,last_bit,,myvar

Can you think of a similar cunning shortcut to answer the first question?

I really do like your solution... very "out of the box". Thanks

[Marcus Tettmar]

Ok. Try this:

Code: Select all

Let>str=first_bit.second_bit.third_bit

//is there a match of "_" followed by something followed by "."
RegEx>_.*\.,str,0,matches,nm,0
If>nm>0
  //yes - so remove the last bit that starts with a "."
  RegEx>\.([^\.]*)$,str,0,matches,nm,1,,str
Endif

[mightycpa]

Your solution:

RegEx>_.*\.,str,0,matches,nm,0

Here's the scary part: I actually recognized what that meant the moment I saw it.

Thanks Marcus.

I still have that one question however, for other purposes.

Is there any danger in doing this?

StringReplace>myvar,last_bit,,myvar

[mightycpa]

RegEx>\.([^\.]*)$,str,0,matches,nm,1,,str

Never mind. Got it.