replace one quote symbol into two quotes in informix

Question

I am working on informix-4gl.my programs is about to adds and update user information from one tables to many tables.In case, there were information from user that contains "'" symbol or single quote such as the purchaser name or user address.My problems is when I update the tables,the information that contains single quote symbols will not updated.And now I had found that there must be an double quotes to enter the values that was in different online server.Now, I am going to change single quotes to double quotes.I had tried changing code like this but its reads only single quotes.

          LET rmks_lgth = LENGTH(p_crsell.crse_purc_nme)    
  FOR a = 1 TO rmks_lgth                          
    IF p_crsell.crse_purc_nme[a] = "'" THEN       
     LET p_crsell.crse_purc_nme[a] = "''"        
   END IF                                        
  END FOR              

I had tried to change the codes in order to produce double quotes.the quotes must be double up for every input that contains "'" quote symbol.

    LET rmks_lgth = LENGTH(p_crsell.crse_purc_nme)    
  FOR a = 1 TO rmks_lgth 
  FOR b = 1 TO rmks_lgth                         
    IF p_crsell.crse_purc_nme[a] = "'" THEN       
     LET p_crsell.crse_purc_nme[a] = "'"  
     LET p_crsell.crse_purc_nme[b] = "'"      
   END IF                                        
  END FOR        
  END FOR      

codes above will only produce output

''

where there is no other values. someone names ceinmark had suggest that "I need create a 4GL function to found the quote , then strip the string in two at the point you found it and concat the "new" quote with this two parts of the string."

Answer 1

Rahim, the solution bellow is only for 4GL code , do not apply to Informix SQL Statement. Easily this can be rewrite to Informix Stored Procedure (but I know as SP this will not solve your problem) :

MAIN
  DEFINE p_sql varCHAR(200)
  DEFINE p_dest varCHAR(200)
  DEFINE i,x,z INTEGER

  LET p_sql = arg_val(1)

  LET x=1
  LET p_dest=''
  FOR i = 1 TO length(p_sql)
    IF p_sql[i,i] = '"' THEN
      LET p_dest=p_dest,p_sql[x,i],'"'
      LET x=i+1
    END IF
  END FOR

  LET i=length(p_sql)
  IF x < i THEN
    LET p_dest=p_dest,p_sql[x,i]
  END IF

  DISPLAY p_sql
  DISPLAY p_dest
END MAIN

The test result:

$ fglgo x "test 'one' "
test 'one'
test 'one'

$ fglgo x 'test "one" '
test "one"
test ""one""

$ fglgo x 'test "one"'
test "one"
test ""one""

$ fglgo x 'test "one" x'
test "one" x
test ""one"" x