SQL Substring function

Question

I have the following statement. When I don't use the substring function I get over 20 rows selected. When I use this statement I get zero. I'm just wondering what I would need to fix in this statement to achieve the same results.

SQL> select * from
  2  (select
  3  sql_fulltext, executions
  4  from v$sql
  5  order by elapsed_time desc)
  6  where substr(sql_fulltext,1,30) like '%table_name%';

May be stupid comment, but is the string "table_name" in the first 30 characters of sql_fulltext? — asantaballa
– asantaballa, Commented Sep 29, 2013 at 13:29
No it doesn't. Here is the Q I'm trying to solve "2. Display the SQL statements (first 30 characters only) and the number of executions for all SQL commands that have been executed for those SQL statements that include the word table_name.(Hint – Escape clause)" — PrgmRNoob
– PrgmRNoob, Commented Sep 29, 2013 at 13:30

asantaballa · Accepted Answer · 2013-09-29 13:34:39Z

1

select * from
(select
  sql_fulltext
, substr(sql_fulltext,1,30) ShortText
, executions
from v$sql
order by elapsed_time desc)
where sql_fulltext like '%table_name%'

So select in the full name, and use ShortName for the result.

answered Sep 29, 2013 at 13:34

asantaballa

4,0581 gold badge24 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

There is no need for the derived table, the where condition can be applied directly.

asantaballa Over a year ago

Agree, @a_horse_with_no_name, but did not want to confuse the issue. Was not sure if golf_pro90 might have it there for a different reason.

Collectives™ on Stack Overflow

SQL Substring function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related