Parse input with either a space or newline

I have a string where I'm trying to pull out a database table name. I don't know if the string contains a newline, a carriage return. I'm not sure how to split that part out. Here is the string. This is being pulled from a database query.

INSERT INTO DWH_CCP.TGT.CCP_ATTR_XREF
SELECT
ATTR_KEY
,ATTR_NAME
,ATTR_DESC

my code is the following:

    while($string =~ / INTO (\S+?\.\S+?)[\s\n\r()]/gi) {
      my $table = $1;
    }
    print "\ntable is $table\n";

however the output reads:

table is DWH_AIR.DWH_CCP.TGT.CCP_ATTR_XREFSELECTATTR_KEY,ATTR_NAME,ATTR_DESC

Solution

There are multiple issues with the code itself, and the regex is overly complicated.

One way

my ($table_name) = $string =~ / INTO (\S+)/i;

The regex match returns the list of captures when in the list context, here provided by those parenthesis on the left-hand side. If there are no matches that variable stays undef. The \S matches all non-space characters, so including the period (.), and thus \S+ stops matching at any white space (what includes newline and carriage return). The i modifier is there in case INTO may be lower-cased.

Then you can test the $table_name, to check whether the pattern was in the $string.

If for some reason you wanted to segregate this test from the rest of the code you can put it inside an if but you'd still have to declare the variable to hold the name outside of the if condition and block if it is needed outside, so why bother with if then.^†

In case that it's all done inside a block you could go like

if ( my ($table_name) = $string =~ / INTO (\S+)/i ) {
    # process/use $table_name...
}
# No $table_name here

If there are more details that motivate the use of while and that complicated pattern in the question then please reveal them -- include them in the question.

To comment on the regex itself, given the shown table name all you need for the name itself is that (\S+), which should follow INTO .

If you wanted to be more precise with the pattern, the shown name is matched by [A-Z_.]+, but it would be unwise to exclude lower-case letters -- and why exclude numbers? -- so then perhaps

/ INTO ([\w.]+)/i

Add to the character class whatever other particular characters may be in the name.

The main regex references: perlretut and perlre.

^† The while shown in the question is unneeded here, unless your $string really has multiple insert statements with table names. You can use it instead of an if though but it is misleading if the $string contains only one table name.

The other problem with that code is that the $table does not exist outside of the while block since the variable is declared inside so that is its "scope", that block.