Shell Programming and Scripting

Hi all,

I am trying to search a SQL string and trying to find the table name in the query.

For e.g.

Select a,b,c,d,e,f from ITS.table where ITS.x=name

In the above string i need to identify the word ITS and replace with owner.
And also the name is not always ITS, it can be any word. So i need to identify the from keyword and the first '.' symbol and identify the word and then try to replace it.

I am trying to do it in PERL .

thanks in advance

Code:

s/\S+\./owner./g

\S matches non-spaces and + means as many as possible, and \. is a literal dot. So any dot with a sequence of non-space characters before it gets replaced with "owner.".

Hi Era,

Thanks for the reply.

But first of all I need to identify the string inbetween the from and . and then need to replace the string identified with owner.

Because the string is not always static for different query the table names changes.

Thanks in advance

I don't understand. Are there places where the table should not be renamed to "owner"? Or are there tables which should not be replaced? Or are there other lines with dots where you don't want the substitution to happen? Then do it conditionally:

Code:

s/\S+\./owner./g if m/ from /;

... assuming the word "from" with spaces on both sides is a good way to identify the lines where the substitution needs to happen.

Or do you even have lines with more dots where not all of them concern the same table?

Code:

if (m/ from (\S+)\.) { s/$1\./owner./g }

This will take the word after the "from" and only replace that, on that line. (This is what you were asking all along, I know, but if the conditions are more relaxed then a simpler code is better, and you were not very clear on the precise conditions).

Ah, more than one way to do it... I too think that "from" is the key here because the XYZ.TABLENAME always follows the word "FROM".

SQLstr =~ /[FROM | from]\s(\w+)\./;

OK so... find FROM (in upper or lowere case) followed by one or more spaces \s followed by one or more WORD \w+ characters (i.e. letters) followed by a literal dot \.

Now the parentheses around \w+ actually captures that part of the match in $1, so next you can substitute that piece alone....

SQLstr =~ s/$1/OWNER/e

The "e" causes the $1 to be EVALUATED so that the resulting string is expanded before the substitution....

this will not work:

Code:

SQLstr =~ /[FROM | from]\s(\w+)\./;

[FROM | from] is a character class which matches any of the characters inside in no particular order. Should be:

Code:

$SQLstr =~ /(FROM|from)\s(\w+)\./;

or maybe:

Code:

$SQLstr =~ /from\s(\w+)\./i;

although using case insensitive matching might not be appropriate in this situation since SQL statements tend to be written in all upper or all lower case.

You also do not need the "e" option in the last regexp. $1 does not need to be evaluated, it needs to be interpolated and the left side of a substitution regexp is interpolated like a double-quoted string.

The other mistake is that the table name should be replaced elsewhere on that line, too (and maybe elsewhere, too; mahalakshmi was not very clear on the broader context of this).