3

I have a table in database(oracle sql) which somehow contains one special character at the end which I dont know.I am trying to get that special character from its entry in table tab.

SQL> SELECT tname from tab where tname='OPERATION';

no rows selected

SQL> SELECT tname from tab where tname like 'OPERATION%';

TNAME
--------------------------------------------------------------------------------
OPERATION

SQL> SELECT length('OPERATION') from tab where tname like 'OPERATION%';

LENGTH('OPERATION')
------------------------
                       9

SQL> SELECT length(tname) from tab where tname like 'OPERATION%';

LENGTH(TNAME)
-------------
           10

So , there is one special character after char N in string OPERATION . I tried getting the last character using substr but that did not help.

How can I find this special charater ( or even ascii value of this character ) ?

And how can I run select on the data in this table ?

0

1 Answer 1

7

You can try the dump function to get the actual byte values stored. For example, I just created a table "OOUPS ", with a space at the end.

> select table_name, dump(table_name) from user_tables;
...
OOUPS   Typ=1 Len=6: 79,79,85,80,83,32
...

You can see 32 (0x20 hex) as the last char - that's an ASCII space.

Once you've found what the extra character is, you can query the table using double quotes around the name. This is case-sensitive, so make sure you match that too. e.g.:

create table "OOUPS " (a int);
insert into "OOUPS " (a) values (42);
commit;
select * from "OOUPS ";

And of course:

alter table "OOUPS " rename to more_sane_name;

If you can't type the strange character easily, simplest way would be to create an SQL script, or pipe the exact character sequence to SQL*Plus:

OOUPS   Typ=1 Len=6: 79,79,85,80,83,13
$ echo -e 'alter table "OOUPS\r" rename to saner_name;\nexit;' | sqlplus user/pass
...
Table altered.
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.