Hi

I've got the following block (in part!) to read information about column names in a MySQL table.

Code:

@val = split (/,/, $val[1]);
s/^'(.*)'$/$1/ foreach (@val);

The column names in raw format would output:

Code:

'foo1','foo2','foo3','foo4'

What it does is split on commas, and then trim the single 'quotation' marks from the end of each word.

My problem derives from the fact that I have a few records in my databases that include commas. When I run the above sub it splits the record at the end of each word because it picks out the commas, and therefore outputs incorrect information.

My question is, how can I modify the above to split each record only when the comma is found after a single quotation mark? I do not want to split by any comma, only if the comma is found after the single quote.

Thank for any help you can give.

- wil

Um:

@val = split (/',/, $val[1]);



Or you could use word boundaries using \b

Hi Paul

I don't think that will work, because the last entry will not have a comma after the ' therefore it might screw up the last record ?

Cheers

- wil

wouldn't you want:

@val = split (/','/, $val[1]);

then chop the leading/trailing '

I got around the problem with:


Can anyone spot anything wrong with the above?

Thanks

- wil

Yeah, this line makes no sense:

s/'/$1/g foreach (@val);

It works, though

- wil

Its poor code

$1 isn't defined, hence serves no purpose.

my @val = split(/',/, $val[1]);
s/^'// foreach @val;

That should work...


Adrian

Hi Adrian

Yeah, but I needed it to delete the ' at the end of the line too, which calls for either two lines, like so:


Or I could try adn throw it all on one line with:


?



- wil

s,^'|'$,,g foreach @val;

That code will take:
'foo','bar','baz'
and turn it into
@val = ["foo", "bar", "baz"]

split will handle the ending ' and the comma, and the regex handles the beginning '


Adrian

Actually it would print:

'foo'bar'baz'

This should work:

my @val = split /',/, $val[1];

s/'//g foreach @val;

oops, my bad, actually it should produce:
foo bar baz'

note that:
will take out ALL single quotes from it. might not be desired unless you know for sure that no strings will contain a single quote...


Adrian

>>oops, my bad, actually it should produce:
foo bar baz'
<<

No it wouldn't

You are splitting ', so every other ' would be left in.

'foo','bar','baz'

Now you've all compeltly lost me. It's been a hell of a long day, and I don't know my commas from my quotations anymore. Where were we? The code I have now works great (not on this machine, though).

It splits each field by ', and either 0 or 1 ' after. It then just gets rid of all ' quotation marks from the string, as there shouldn't be any in my database. You can't have any quotation marks in an ENUM colum can you?

- wil

There! That should work (I tested it this time )

Yes, and so you would split by:
leaving: 'foo 'bar 'baz'
and that second regex would have stripped the first ' leaving:
foo bar baz'


Adrian

The way the string is actually slurped in is like this:

It's an ENUM field in a SQL database if you hadn't of guessed.

I'm using this to split it now:


- wil

Why not just take off the beginning and ending 's at the first regex?



Adrian

Hi Adrian

I do tend to code larger chunks than neccessary, because I am still very much learning Perl and I like to be able to view the steps on my screen in a logical sense. Once I'm crystal with what I'm doing I will then try and trim and optimise my code.

Thanks for your regex though. That looks readable and understandable enough as it is. However, wouldn't I'm concerned with the last result. Look at the data again. After the first line of your regex that would leave you with:

Code:

foo1','foo2','foo3','foo4

How would that handle the final element? Would that be split correctly?

Thanks

- wil

It would split fine...
try it:
perl -e '$foo="foo1'\'','\''foo2'\'','\''foo3'\'','\''foo4"; @foo = split(/'\'','\''/, $foo); use Data::Dumper; print Dumper(\@foo);'

It's like doing:
$foo = "foo:bar:baz";
@foo = split(/:/, $foo);
would become ["foo", "bar", "baz"]


Adrian

One thing I didn't really understand about your regex was the [^(]* at the beginning. There a reason for it?


Adrian

Yeah, it should take out the ( at the beginning ot the string? It's an ENUM column from a database, so my data would look like:

ENUM ('foo1','foo2','foo3')

- wil