Handle single tick entry 1

[dreampolice]

I have a Web Front end that populates an Oracle 9i database.
I have to add a script in the Front end web language (PHP) to handle single tick marks because Oracle field values dont handle single tick entries and it wont enter the record in the database. The script substitutes single tick for double ticks and that allows a single tick to enter the database value.

Is there something in the Oracle database I can write or create to handle single tick entry in a field value?

I need it for 6 of my varchar2 field values in one table.
I assume this would be done using a trigger but not sure how to do it. Please advise.

 

[SantaMufasa]

Dream,

There are several ways to embed single quote marks (single ticks) into database-stored values. Since your specifications regarding your front-end application's capabilities and flexibilities are sparse, I must guess at the best alternative for you.

In Oracle, if you want a single quote to appear in a string, then you use two consecutive single quotes, themselves inside a string bounded by beginning and ending quotes.

The code below uses the Oracle REPLACE function to substitute any one single-quote occurrence with two single-quote occurrences. Notice in the sample data that there are three embedded single quotes: 1) a beginning single quote, 2) a single quote that acts as a possessive apostrophe, and 3) an ending single quote. The accompanying code transforms each of these three quotes into a pair of single quotes:

SQL> select * from dream;

TXT
------------------------------------------
'This string's contents include 3 quotes.'

1 row selected.

SQL> select replace(txt,'''','''''') from dream;

REPLACE(TXT,'''','''''')
------------------------------------------------
''This string''s contents include 3 quotes.''

1 row selected.

Let us know if this resolves your need.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dreampolice]

I should have been more clear because I meant to say in the insert statement the single tick values entered by the user dont get entered in the database. That means if someone enters a table record into the database and any or all of the fields have a single tick in the varchar2 field value, the record doesnt get inserted into the database.

Currently I handle this situation in my PHP web front end in the action page by substituting a single tick with two ticks and it goes into the database as a single tick. I was hoping to have some sort of automation in the Oracle database to handle this the same way I am doing it with PHP except instead of web front end action page, fix it in the database instead.

 

[SantaMufasa]

I was hoping to have some sort of automation in the Oracle database to handle this the same way I am doing it with PHP

Is that not what the REPLACE function is doing, above?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dreampolice]

Thanks,

This would be correct for my insert statement in the PHP web Oracle insert area?

insert into sales 
  2  (store, book) 
  3  values 
  4  (replace(store,'''',''''''),replace(book,'''',''''''));

 

[SantaMufasa]

Absolutely correct. Try it out and let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dreampolice]

Thanks