CODEWITHSUNDEEP
sqloracle-databasesql-insert
Sathesh
Sathesh

Reputation: 496

Insert a column with single quote or Apostrophe in Oracle

I am trying to insert into Table Users from Person table.

However, The first_name column in the person table contains apostrophe in the name (Eg- Rus'sell) which is preventing me from successful insertion. How do I fix this?

INSERT INTO USERS VALUES (SELECT FIRST_NAME,.........FROM PERSON);

Upvotes: 1

Views: 7408

Answers (3)

Sathesh
Sathesh

Reputation: 496

Apologies for the obscurity if any in the question. The query I was working with was a long insert query with multiple joins.

To sum it was a stored proc where I was doing an insert, for which the data is given by long select query with multiple joins. One of the column is the FIRST_NAME column which had some values with Apostrophe in it (Rus'sell, Sa'm).

The Insert statement values were being generated as below which was causing an 'ORA-00917: missing comma' error. 

INSERT INTO TABLE_NAME values (314159,0,'Rus'sell','Parks','...........)

I fixed this by Replacing the column in the select from a single quote to two single quotes, before giving it to the insert statement which basically solved the issue.

REPLACE(FIRST_NAME,'''','''''') AS FIRST_NAME

Hope it helps.

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

INSERT INTO USERS VALUES (SELECT FIRST_NAME,.........FROM PERSON);

First of all, your insert statement is syntactically incorrect. It will raise ORA-00936: missing expression. The correct syntax to insert multiple records from source table is:

INSERT INTO table_name SELECT columns_list FROM source_table;

The VALUES keyword is used to insert a single record into table using following syntax:

INSERT INTO table_name(columns_list) VALUES (expressions_list);

If you already have the value stored in another table, then simple INSERT INTO..SELECT FROM should work without any issues. However, if you are trying to INSERT INTO..VALUES having single quotation marks, then the best way is to use Quoting string literal technique The syntax is q'[...]', where the "[" and "]" characters can be any of the following as long as they do not already appear in the string.

  • !
  • [ ]
  • { }
  • ( )
  • < >

You don't have to worry about the single-quotation marks within the string.

create table t(name varchar2(100));
insert into t values (q'[Rus'sell]');
insert into t values (q'[There's a ' quote and here's some more ' ' ']');
select * from t;

NAME
-----------------------------------------------
Rus'sell
There's a ' quote and here's some more ' ' '

Upvotes: 3

OldProgrammer
OldProgrammer

Reputation: 12179

I don't think your question is showing the complete details, because I can execute the following statements without any problem:

create table person( first_name varchar2(100));
create table users( first_name varchar2(100));
insert into person values ('Rus''sell');
insert into users select first_name from person;

Upvotes: 1

Related Questions