OraFAQ Forum: SQL & PL/SQL » Select query like operator getting wrong data

Home » SQL & PL/SQL » SQL & PL/SQL » Select query like operator getting wrong data (11g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Select query like operator getting wrong data [message #573247]

Tue, 25 December 2012 04:38

cplusplus1
Messages: 30
Registered: October 2012
Location: usa

Member

I am using the following query with like 'T_%', i am getting 80 rows out of which the first table_name doesn't even have a beginning part 'T_%' , am i doing something wrong.

the first table name has not started with 'T_', why is it appearing.

*********************************************************************
SELECT 'Truncate table epic500.'||table_name
FROM user_tables where table_name like 'T_%' order by table_name;
*********************************************************************
output:
Truncate table epic500.TEMP_ENC_DEL
Truncate table epic500.T_ACCOMMODATION_CODE

Thank you very much for the helpful info.

Report message to a moderator

Re: Select query like operator getting wrong data [message #573249 is a reply to message #573247]

Tue, 25 December 2012 04:52

Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

See:
1/ What are the joker characters of LIKE
2/ ESCAPE option of LIKE
in Database SQL Reference.

Regards
Michel

[Updated on: Tue, 25 December 2012 04:53]

Report message to a moderator

Re: Select query like operator getting wrong data [message #573250 is a reply to message #573249]

Tue, 25 December 2012 04:58

Littlefoot
Messages: 16928
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Your problem is in not knowing Oracle wildcard characters very well. % replaces one or many characters, but underscore (_) you used is also a wildcard character - it replaces a single character. Therefore, you need to escape it, just as Michel said. Here's an example:

SQL> select table_name from user_tables order by table_name;

TABLE_NAME
------------------------------
AUTHENTICATION_USER
DEPT
EMP
EXCEPTIONS
MV_BARKOD
ORDER_TEST
PLAN_TABLE
QUEST_SL_TEMP_EXPLAIN1
TEST
TOAD_PLAN_TABLE
T_TEST
USERLOG

12 rows selected.

SQL> -- Your query:
SQL> select 'Truncate table epic500.'||table_name
  2  from user_tables where table_name like 'T_%'
  3  order by table_name;

'TRUNCATETABLEEPIC500.'||TABLE_NAME
-----------------------------------------------------
Truncate table epic500.TEST
Truncate table epic500.TOAD_PLAN_TABLE
Truncate table epic500.T_TEST

SQL> -- Escape the underscore character (because it is a single character wildcard)
SQL> select 'Truncate table epic500.'||table_name
  2  from user_tables where table_name like 'T\_%' escape '\'
  3  order by table_name;

'TRUNCATETABLEEPIC500.'||TABLE_NAME
-----------------------------------------------------
Truncate table epic500.T_TEST

SQL>

Report message to a moderator