From: Kev Jackson <kevin.jackson@...>
Date: 2005-10-06T20:27:29+09:00
Subject: Re: regexp to replace comma in text

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\sHH\: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