Single Row Function in sql

Single Row Function in sql

Single row function divided into 4 types

1) String Function

2) Math Function

3) Date Function

4) Date Conversion Function

String Function of Single Row Function

a) Length():  This function used to count number of characters in given string including spaces.

     Ex: select length('Maha rashtra') from dual

b) initcap(): This function is used to convert first letter of every word into capital letter.

    Ex: select initcap('Maha rashtra') from dual

c) ltrim(): Remove blank spaces from left hand side of string;

   Ex:select ltrim(‘        oracle’) from dual

d) rtrim(): Remove blank spaces from right hand side of string;

    Ex:select ltrim(‘oracle     ’) from dual

e) trim(): Remove blank spaces from left and right hand side of string;

    Ex:select ltrim(‘        oracle        ’) from dual;

f) vsize(): cound number of  bytes occupied by given string

   Ex:select vsize(‘oracle’) from dual

g) lpad: Used to combining  given character from left hand side of string

   Ex:select lpad(‘oracle’,15,’*’) from dual

h) rpad: Used to combining  given character from right hand side of string

   Ex:select rpad(‘oracle’,15,’*’) from dual

i) translate: translate given character with specified character

   Ex:select translate (‘i am from oracle,’o’,t’) from dual

j) replace():replace given character with specified character

  Ex:select translate (‘i am indian’’n’,t’) from dual

k) substr(): Return the part of string from main string

    Ex: select substr (‘india’0,4’) from dual

l) soundex():Used to retrieve the name whose pronounciation is same but spelling are  defferent

   Ex:select * from from names where soundex(name)=sound(‘murthy’);

m) upper(): convert into capital letter

    Ex:select upper(‘indian’) from dual

n) lower(): Convert into small letter

    Ex:select upper(‘INDIAN’) from dual

o) instr(): Returns position of character

   Ex:select instr(‘india’,’d’)