CODEWITHSUNDEEP
sqloracle-database
user1263981
user1263981

Reputation: 3147

LEFT function in Oracle

I am looking in an Oracle (10g) stored procedure and come across the following LEFT/RIGHTfunction.

TO_DATE(LEFT('01-Jun-1201',9))

In the Toad editor, I am not able to run this function and have to change it to LPAD

TO_DATE(LPAD('01-Jun-1201',9))

The stored procedure is running fine with LEFT/RIGHT function but it runs faster if I use LPAD/RPAD.

Is there any LEFT Function in Oracle and if not then why is the stored procedure running fine?

SELECT
    SUM(DECODE(SIGN(TO_DATE(LEFT('01-Jun-12', 9)) - TO_DATE(logdate)),
               -1, totaltime, 0, totaltime, 0)) AS totaltime
  FROM AREA2.v_area
  WHERE logdate >= TO_DATE(RIGHT('01-Jun-12', 9))
    AND logdate <= TO_DATE('30-Jun-12')

Upvotes: 19

Views: 285615

Answers (4)

Oğuz T&#252;rkmen
Oğuz T&#252;rkmen

Reputation: 1

--LEFT FUNCTION
create or replace function LEFT(value in varchar2,part in int) return varchar2 is
wreturn varchar2(500);
begin
select substr(value,1,part) into wreturn from dual;
return wreturn;
end;



--RIGHT FUNCTION
create or replace function RIGHT(value in varchar2,part in int) return varchar2 is
wreturn varchar2(500);
begin
select substr(reverse(value),1,part) into wreturn from dual;
return wreturn;
end;

Upvotes: 0

MarvinM
MarvinM

Reputation: 721

I've discovered that LEFT and RIGHT are not supported functions in Oracle. They are used in SQL Server, MySQL, and some other versions of SQL. In Oracle, you need to use the SUBSTR function. Here are simple examples:

LEFT ('Data', 2) = 'Da'

->   SUBSTR('Data',1,2) = 'Da'

RIGHT ('Data', 2) = 'ta'

->   SUBSTR('Data',-2,2) = 'ta'

Notice that a negative number counts back from the end.

Upvotes: 44

Vectorjohn
Vectorjohn

Reputation: 411

LEFT is not a function in Oracle. This probably came from someone familiar with SQL Server:

Returns the left part of a character string with the specified number of characters. 

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
LEFT ( character_expression , integer_expression )

Upvotes: 0

APC
APC

Reputation: 146239

There is no documented LEFT() function in Oracle. Find the full set here.

Probably what you have is a user-defined function. You can check that easily enough by querying the data dictionary:

select * from all_objects
where object_name = 'LEFT'

But there is the question of why the stored procedure works and the query doesn't. One possible solution is that the stored procedure is owned by another schema, which also owns the LEFT() function. They have granted rights on the procedure but not its dependencies. This works because stored procedures run with DEFINER privileges by default, so you run the stored procedure as if you were its owner.

If this is so then the data dictionary query I listed above won't help you: it will only return rows for objects you have rights on. In which case you will need to run the query as the stored procedure's owner or connect as a user with the rights to query DBA_OBJECTS instead.

Upvotes: 9

Related Questions