CODEWITHSUNDEEP
sqloracle-databasesql-update
mradul
mradul

Reputation: 550

Use special character '%' to append the string in update statement

I need to update a table where the table column is of varchar2 datatype and need to update the value of columns with '%'.

for example --

create table test (id number, name varchar2(20));

insert into test values (1, 'smith');
insert into test values (2, 'allen');

Now we need to update the values in NAME column to smith'%'

So it should also include the single quotes in the string.

I am able to update it to smith% but it should be smith'%'

update test
  set name = 'smith'||'''%'''
where id = 1;

SQL Error: ORA-00911: invalid character

Upvotes: 0

Views: 8328

Answers (3)

user7584647
user7584647

Reputation:

SQL> set define off;

verify this link

how-to-enter-special-characters-like-in-oracle-database

Upvotes: 1

Aleksej
Aleksej

Reputation: 22959

Your query works perfectly in SQLPlus:

SQL> update test
  2    set name = 'smith'||'''%'''
  3  where id = 1;

1 row updated.

SQL> select * from test;

        ID NAME
---------- --------------------
         1 smith'%'
         2 allen

This could be another way, avoiding the need to double the quotes:

SQL> update test
  2    set name = 'allen'|| q'['%']'
  3  where id = 2;

1 row updated.

SQL> select * from test;

        ID NAME
---------- --------------------
         1 smith'%'
         2 allen'%'

Or even, avoiding the ||:

SQL> update test
  2    set name = concat(name, q'['%']')
  3  where id = 1;

1 row updated.

SQL> select * from test;

        ID NAME
---------- --------------------
         1 smith'%''%'
         2 allen'%'

Upvotes: 1

RoundFour
RoundFour

Reputation: 347

Actually your statement should work, I just tested it in my database.

update test
  set name = concat('smith', '''%''')
where id = 1;

Upvotes: 0

Related Questions