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

Question

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

The update should not be failing AFAIK. Are you sure this is the actual query you ran? — Tim Biegeleisen, Feb 22 '17 at 07:23
It worked for me. @TimBiegeleisen is right. Are you sure you ran the query ok? — pablomatico, Feb 22 '17 at 07:26
@pablomatico Perhaps Oracle has some mode where concatenation using `||` is not possible? — Tim Biegeleisen, Feb 22 '17 at 07:26
Your example doesn't even need the concatenation: `'smith''%'''` will do just fine. But I guess what you _meant_ is `set name = name||'''%'''`: http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=fbf0f2654ddccb165c991c3cee6719f2 — , Feb 22 '17 at 07:40
Which client are you using, in case that matters? The only way I can see you getting that error from what you posted is if you had done `set sqlterminator off`` or the equivalent for your client, but that would affect all statements, not just one that has a % in it. Are you maybe calling this from some shell or script that is interpreting the % before it reaches Oracle? — Alex Poole, Feb 22 '17 at 09:04

score 1 · Answer 1 · edited May 23 '17 at 10:29

1

SQL> set define off;

verify this link

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

edited May 23 '17 at 10:29

Community

1
1

answered Feb 22 '17 at 07:25

1

This doesn't affect how % is handled. There are no ampersands in the OP's question. – Alex Poole Feb 22 '17 at 08:55
all comes under special char – Feb 22 '17 at 09:00
1

`set define off` has nothing to do with the `%` sign. – Feb 22 '17 at 12:39

score 1 · Answer 2 · answered Feb 22 '17 at 07:31

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'%'

score 0 · Answer 3 · answered Feb 22 '17 at 07:25

0

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

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

answered Feb 22 '17 at 07:25

RoundFour

347
1
7

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

3 Answers3