Home » SQL & PL/SQL » SQL & PL/SQL » Select query like operator getting wrong data (11g)
Select query like operator getting wrong data [message #573247] Tue, 25 December 2012 04:38 Go to next message
cplusplus1
Messages: 40
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.
Re: Select query like operator getting wrong data [message #573249 is a reply to message #573247] Tue, 25 December 2012 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
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 Go to previous messageGo to next message
Littlefoot
Messages: 19650
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>
Re: Select query like operator getting wrong data [message #573252 is a reply to message #573250] Tue, 25 December 2012 06:02 Go to previous messageGo to next message
cplusplus1
Messages: 40
Registered: October 2012
Location: usa
Member
Thank you very much to both of you, Merry Christmas.

I work mostly on sql server.
Re: Select query like operator getting wrong data [message #573253 is a reply to message #573252] Tue, 25 December 2012 07:13 Go to previous message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
cplusplus1 wrote on Tue, 25 December 2012 07:02
I work mostly on sql server.


Then you don't know LIKE in SQL Server either. Same as in Oracle, SQL Server honors % and _ as wildcards.

SY.

[Updated on: Tue, 25 December 2012 07:13]

Report message to a moderator

Previous Topic: query no check wrong mapping
Next Topic: SQL for Parent-Child Hierarchy
Goto Forum:
  


Current Time: Wed Oct 01 08:45:06 CDT 2014

Total time taken to generate the page: 0.10955 seconds