Thread: Regarding functions in sql

Hi,

I am trying out various functions in SQL.

I did not understand the second form of INSTR.it looks like this
INSTR(COL_name,string,num1,num2).Can anyone explain?

And in TRIM function even though I specified lead it trims both the ends
Ex:-select ename,trim('a' in ename)lead from emp;

In round function,e=what actually happens if we gave the second argument negative?

ex:-select round(45.3,-2) from dual;why does this return 0??

INSTR(COL_name,string,num1,num2).

I am not aware of this function.

Ex:-select ename,trim('a' in ename)lead from emp;

for lead trim, u should give as LTRIM(str). not like that.

In round function,what actually happens if we gave the second argument negative?

It will rounds off the digits before decimal point(right side of decimal point).
45.34 - 2 digits in right. its 0.

Hi Radhi,

I think i have to answer for INSTR function alone, Barbie has already explained about TRIM & ROUND functions :-)

Basically, Instr function returns numeric position of the named string passed as 2nd parameter. m & n are optional, u can provide a position m to start searching and the occurence n of the string. If m and n are provided, then the default value is 1, meaning start the search at the beginning of the search and report the first occurrence.

SQL> select instr('RADHIRANI','A'), instr('RADHIRANI','A',1,1), instr('RADHIRANI','A',1,2) from dual;

INSTR('RADHIRANI','A') INSTR('RADHIRANI','A',1,1) INSTR('RADHIRANI','A',1,2)
---------------------- -------------------------- --------------------------
2 2 7

*** Innila ***

Hello Every body,
I am knew this site, please extend your hands to help more inthis regard,

thanks a lot to evey one who are all in this site.

Regards,
Kiran

Originally Posted by Radhi09

Hi,

I am trying out various functions in SQL.

I did not understand the second form of INSTR.it looks like this
INSTR(COL_name,string,num1,num2).Can anyone explain?

And in TRIM function even though I specified lead it trims both the ends
Ex:-select ename,trim('a' in ename)lead from emp;

In round function,e=what actually happens if we gave the second argument negative?

ex:-select round(45.3,-2) from dual;why does this return 0??