0

I would like to know how I can insert regular expression in a table column in a Oracle table.

insert into rule_master(rule)
values('^[0-how #'ff#'9]+$') where rule_id='7'

...but I am getting error syntax near where is wrong. I tried this with and with out single quotes. Please suggest me a solution.

oetoni
  • 3,269
  • 5
  • 20
  • 35
srikanth peddini
  • 1
  • 4
  • 1
    You can't INSERT using a WHERE, because no row exists yet. (If it did, your INSERT would fail as a duplicate if you've properly set up your tables.) You UPDATE using WHERE, you INSERT to add a new row. Find a SQL tutorial. – Ken White Jan 18 '18 at 17:42
  • insert into abc values( 'how'pn'#'='#'#'the B''''); i tried this, but got the error Error report - SQL Error: ORA-00917: missing comma update abc set rollno='''how'pm''#'='#the is 'b''' where rollno=101; this update is also showing error – srikanth peddini Jan 18 '18 at 17:44
  • 1
    Possible duplicate of [Oracle special characters](https://stackoverflow.com/questions/3669711/oracle-special-characters) – jim31415 Jan 18 '18 at 20:22
  • Not sure why that’s been picked as a duplicate - this has nothing to do with substitution varibales. – Alex Poole Jan 18 '18 at 21:17

1 Answers1

3

Aside from the invalid syntax using where, you also need to escape the single quotes in your string by doubling them up:

A single quotation mark (') within the literal must be preceded by an escape character. To represent one single quotation mark within a literal, enter two single quotation marks.

so with a normal text literal:

insert into rule_master(rule) values('^[0-how #''ff#''9]+$')
                                               ^^   ^^

or you can use the alternative quoting mechanism syntax, if you can identify a quote_delimiter character that will never appear in the value (or at least not immediately before a single quote); e.g. if you know @ will never appear you can use a pattern like:

values(q'@<your actual value>@')

i.e.:

insert into rule_master(rule) values(q'@^[0-how #'ff#'9]+$@')
                                     ^ ^                  ^

If the where part is supposed to be populating that column at the same time then the syntax would be more like:

insert into rule_master(rule_id, rule)
values(7, q'@^[0-how #'ff#'9]+$@')

and if a row with that ID already exists you should be using update rather than insert:

update rule_master
set rule = q'@^[0-how #'ff#'9]+$@'
where rule_id = 7

or perhaps merge if you aren't sure.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thank you, it is working now and if possible share the concept of deliiter and escaping character – srikanth peddini Jan 18 '18 at 18:07
  • 1
    Aren't those explained in the documentation I linked to? In the first version, from "To represent one single quotation mark within a literal, enter two single quotation marks", and in the second it has various things to say about *`quote_delimiter`*... – Alex Poole Jan 18 '18 at 18:12