Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.
share|improve this answer
    
In my case ,output of dump is Len=15: 90,65,73,70,95,79,80,69,82,65,84,73,79,78,13 Special character is \r.How can run select on this ? SELECT * from "OPERATION\r"; doesn't work. –  g4ur4v Aug 1 at 10:09
    
Provided an option to rename that table. (I strongly discourage you from trying to work with it with its current name.) Does that work for you? –  Mat Aug 1 at 10:24
    
Yes it did,Thanks. I am going to get rid of this name. –  g4ur4v Aug 1 at 10:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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