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??:confused:
Re: Regarding functions in sql
[QUOTE]INSTR(COL_name,string,num1,num2).[/QUOTE]
I am not aware of this function.
[QUOTE]Ex:-select ename,[B]trim('a' in ename)lead[/B] from emp;[/QUOTE]
for lead trim, u should give as LTRIM(str). not like that.
[QUOTE]In round function,what actually happens if we gave the second argument negative?[/QUOTE]
It will rounds off the digits before decimal point(right side of decimal point).
45.34 - 2 digits in right. its 0.
Re: Regarding functions in sql
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 ***