From: Robert Klemme <bob.news@...>
Date: 2005-10-06T20:36:48+09:00
Subject: Re: regexp to replace comma in text

Kev Jackson wrote:
> nobuyoshi nakada wrote:
>
>> Hi,
>>
>> At Thu, 6 Oct 2005 19:54:12 +0900,
>> Kev Jackson wrote in [ruby-talk:159336]:
>>
>>
>>> How would you replace the commas embedded in the strings in the SQL?
>>>
>>>
>>
>> line.gsub(/'(?:[^\']|'')*'/) {$&.delete(",")}
>>
>>
>>
> Thanks, it's almost there...
>
> Here's my code
>
> def munge_data(line)
>     unless line == nil
>       # remove star value
>       line.sub!(/'\*', /,'')
>       # replace client
>       line.sub!(/'([A-Z]{3})'/) {
>         val = @client_pks["'"+$1.dup+"'"]
>         val.to_s
>       }
>       # remove commas (",") from inside descriptions/titles/briefs etc
>       line.gsub!(/'(?:[^\']|'')*'/) {$&.delete(",")}
>       # remove TO_Date
>
>
line.sub!(/TO_Date\(\s(\'\d*\/\d*\/\d*)\s[\s\w\/\:]+\',\s\'MM\/DD\/YYYY\sH
H\:MI\:SS\sAM\'\)/,'\1\'')
>
>       data = line.split(",")
>       p data.length
>       #p data[16]
>       line = data.join(",")
>     end
>   end
>
> The regexp seems to work (on visual inspection), but when I split on
> "," I still get varying line lengths, if the only commas where between
> fields I should have all lines.split.length equal.  Unless I'm being
> stupid.
>
> Here's another line,
>
> INSERT INTO ADMUSER.TBLPROJ ( TBLPROJ_ID, PROJ_TITLE, PROJ_NO, STAR,
> PROJ_DATE, PROJ_CATEGORY, PROJ_SECTOR, PROJ_SECTION_CODE,
> PROJ_DEPT_CODE, PROJ_LOC_CODE, PROJ_STATUS, PROJ_STATC, PROJ_BRIEF,
> PROJ_REMARKS, SCHEME_VAL, NEWPROJ, PROJ_CLIENT ) VALUES ( 2225,
> 'TANGGUNGAN TANGGUNGAN BAGI PROJEK PROJEK RKN 7 YANG TELAH SIAP -
> Kerja Membaiki Kerosakan, Tempat Letak Kerita Bertingkat"',
> 'AD418/0501', '*',  TO_Date( '01/01/1986 12:00:00 AM', 'MM/DD/YYYY
> HH:MI:SS AM'), '3', '4', 'D', 'A', 'A', 'E', 'C3', NULL, 'Completed,
> Finalised and Retention Money Released on 21/11/2001.', 11328, 'Y',
> 'JKS');
>
> Not sure how I'm going to do this as I thought that split would be the
> way to go, but these extra commas are scuppering my plans completely
> :)
>
> Kev

You could split along strings with grouping.  Something like

s.split(%r{('(?:[^\']|'')*')})

=> "'aaa', 'bbbb'"
>> s.split(%r{('(?:[^\']|'')*')})
=> ["", "'aaa'", ", ", "'bbbb'"]

Or use #scan

>> s.scan(%r{'(?:[^\']|'')*'})
=> ["'aaa'", "'bbbb'"]

Kind regards

    robert