Home » SQL & PL/SQL » SQL & PL/SQL » Left Function equivalent
Left Function equivalent [message #210774] Fri, 22 December 2006 04:58 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi everybody,
Is there is an equivalent to the 'LEFT' Function of SQLServer in Oracle9i?

thanks in Advance,
bahaa
Re: Left Function equivalent [message #210775 is a reply to message #210774] Fri, 22 December 2006 05:00 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
What the LEFT Function does?

By
Vamsi
Re: Left Function equivalent [message #210777 is a reply to message #210774] Fri, 22 December 2006 05:07 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Well the left function does the following:

SQL>create table t_test(name varchar2(50));

SQL>insert into table t_test(name) values('ABSCD_cs');

SQL>insert into table t_test(name) values('EFGH_pc');

SQL>insert into table t_test(name) values('IJKL_mn');

SQL>Select left(name,5)='cs' from t_test;

I mean if i use the 'substr' instead of 'LEFT' i can not make the equivalent of the '_cs' in the same query,or can i?

Thanks in Advance,
bahaa



Re: Left Function equivalent [message #210779 is a reply to message #210777] Fri, 22 December 2006 05:15 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You have confused me a lot.

Do you mean the following?

Select left(name,5) from t_test;
gives the output as 'cs' or '_cs'

What exactly you are seeking?
What is your query?
What should be your output?
Please elaborate...

If you want to display the characters from 6th position, then you can use
select substr(name,6) from t_test;
By
Vamsi
Re: Left Function equivalent [message #210782 is a reply to message #210774] Fri, 22 December 2006 05:49 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thanks for your replay,
well you have right ..it does not seems to be clear enough yet.
what i want from the query is an indication that the string '_cs' is exist in the 'name' column.
So the following code will return 'Not OK' while the 'name' column has already '_cs' value:

DECLARE
v_Count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_Count FROM T_TEST WHERE SUBSTR(NAME,5)='_cs';
IF v_Count=0 THEN
DBMS_OUTPUT.PUT_LINE('Not OK ');
ELSE
DBMS_OUTPUT.PUT_LINE('OK = '||v_Count);
END IF;
END;
/
Re: Left Function equivalent [message #210784 is a reply to message #210782] Fri, 22 December 2006 06:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you just need to check whether the string contains '_cs' then you can use instr.
SQL> with mytable as (select 'asdf_cs' col_1 from dual union all
  2                   select 'asd_csf' col_1 from dual union all
  3                   select 'asdfcs' col_1  from dual)
  4  select * 
  5  from   mytable
  6  where  instr(col_1,'_cs') > 0;

COL_1
-------
asdf_cs
asd_csf
If you need to check that the last 3 chrs of the string are '_cs' then you could do it like this:
SQL> with mytable as (select 'asdf_cs' col_1 from dual union all
  2                   select 'asd_csf' col_1 from dual union all
  3                   select 'asdfcs' col_1  from dual)
  4  select * 
  5  from   mytable
  6  where  substr(col_1,-3) = '_cs';

COL_1
-------
asdf_cs
Re: Left Function equivalent [message #210786 is a reply to message #210779] Fri, 22 December 2006 06:20 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thanks this is what i am searching for:

SELECT Count(*)
FROM T_TEST
WHERE INSTR(NAME,'_cs') > 0;

Thanks again,
bahaa
Re: Left Function equivalent [message #524939 is a reply to message #210774] Tue, 27 September 2011 12:53 Go to previous messageGo to next message
hriquelme
Messages: 1
Registered: September 2011
Location: chile
Junior Member
select rpad('wena', 1) from dual;

output will return w


Re: Left Function equivalent [message #524950 is a reply to message #524939] Tue, 27 September 2011 15:35 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
hriquelme wrote on Tue, 27 September 2011 13:53
select rpad('wena', 1) from dual;

output will return w




Ok, but what does you answer have to do with the question that was answered 6 years ago?
Re: Left Function equivalent [message #528109 is a reply to message #210774] Fri, 21 October 2011 12:59 Go to previous messageGo to next message
nittinghai
Messages: 1
Registered: October 2011
Location: Miami
Junior Member
Use combination of substr and length to make it equivalent of LEFT function

select substr('12/34',length('12/34')-1,2) A from dual;

Re: Left Function equivalent [message #528112 is a reply to message #528109] Fri, 21 October 2011 13:27 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Wouldn't that be the equivalent of the RIGHT function, though? Returning 2 characters from the RIGHT?

Then you can just use substr with a negative start position, then the position is calculated from the right (end) of the string:

Equivalent of "first two characters from the right"
SQL> select substr('12/34', -2, 2) A from dual;

A
--
34

SQL>

(start 2 characters from the right and return 2 characters)

Equivalent of "first two characters from the left":
SQL> select substr('12/34',1,2) A from dual;

A
--
12

(start at first character from the left and return 2 characters)
Previous Topic: Query to get Total points from various factors
Next Topic: Which is better - CHR(39) or '''' ?
Goto Forum:
  


Current Time: Tue Dec 06 02:31:35 CST 2016

Total time taken to generate the page: 0.07853 seconds