STRING FUNCTIONS
| STRING FUNCTIONS | |||
| Initcap | Upper | Lower | Length |
| Rpad | Lpad | Ltrim | Rtrim |
| Trim | Translate | Replace | Soundex |
| Concat (‘II Concatenation Operation’ | Ascii | Chr | |
| Substr | Instr | Decode | Greatest |
| Least | Coalesce | ||
| INITCAP
(This will capitalize the initial letter of the string) |
|
| Syntax | initcap (string) |
| Ex | SQL> select initcap(‘computer’) from dual;
INITCAP ———– Computer |
| UPPER
(This will convert the string into uppercase) |
|
| Syntax | upper (string) |
| Ex | SQL> select upper(‘computer’) from dual;
UPPER ———– COMPUTER |
| LOWER
(This will convert the string into lowercase) |
|
| Syntax | lower (string) |
| Ex | SQL> select lower(‘COMPUTER’) from dual;
LOWER ———– computer |
| LENGTH
(This will give length of the string) |
|
| Syntax | length (string) |
| Ex | SQL> select length(‘computer’) from dual;
LENGTH ———– 8 |
| RPAD
(This will allows you to pad the right side of a column with any set of characters.) |
|
| Syntax | rpad (string, length [, padding_char]) |
| Ex | SQL> select rpad(‘computer’,15,’*’), rpad(‘computer’,15,’*#’) from dual;
RPAD(‘COMPUTER’ RPAD(‘COMPUTER’ ———————- ———————- computer******* computer*#*#*#*
— Default padding character was blank space.
|
| LPAD
(This will allows you to pad the left side of a column with any set of characters) |
|
| Syntax | lpad (string, length [, padding_char]) |
| Ex | SQL> select lpad(‘computer’,15,’*’), lpad(‘computer’,15,’*#’) from dual;
LPAD(‘COMPUTER’ LPAD(‘COMPUTER’ ——————— ——————— *******computer *#*#*#*computer — Default padding character was blank space. |
| LTRIM
(This will trim off unwanted characters from the left end of string) |
|
| Syntax | ltrim (string [,unwanted_chars]) |
| Ex | SQL> select ltrim(‘computer’,’co’), ltrim(‘computer’,’com’) from dual;
LTRIM( LTRIM ——– ——— mputer puter
SQL> select ltrim(‘computer’,’puter’), ltrim(‘computer’,’omputer’) from dual; LTRIM(‘C LTRIM(‘C ———- ———- computer computer
— If you haven’t specify any unwanted characters it will display entire string. |
| RTRIM
(This will trim off unwanted characters from the right end of string) |
|
| Syntax | rtrim (string, [ unwanted_chars]) |
| Ex | SQL> select rtrim(‘computer’,’er’), rtrim(‘computer’,’ter’) from dual;
RTRIM( RTRIM ——– ——— comput compu
SQL> select rtrim(‘computer’,’comput’), rtrim(‘computer’,’compute’) from dual;
RTRIM(‘C RTRIM(‘C ———- ———- computer computer — If you haven’t specify any unwanted characters it will display entire string. |
| TRIM
(This will trim off unwanted characters from the both sides of string) |
|
| Syntax | trim (unwanted_chars from string) |
| Ex | SQL> select trim( ‘i’ from ‘indiani’) from dual;
TRIM( —– ndian
SQL> select trim( leading’i’ from ‘indiani’) from dual; — this will work as LTRIM TRIM(L —— ndiani SQL> select trim( trailing’i’ from ‘indiani’) from dual; — this will work as RTRIM TRIM(T —— Indian |
TRANSLATE(This will replace the set of characters, character by character) |
|
| Syntax | translate (string, old_chars, new_chars) |
| Ex | SQL> select translate(‘india’,’in’,’xy’) from dual;
TRANS ——– xydxa |
REPLACE(This will replace the set of characters, string by string) |
|
| Syntax | replace (string, old_chars [, new_chars]) |
| Ex | SQL> select replace(‘india’,’in’,’xy’), replace(‘india’,’in’) from dual;
REPLACE REPLACE ———– ———– Xydia dia
|
SOUNDEX(This will be used to find words that sound like other words, exclusively used in where clause.) |
|
| Syntax | soundex (string) |
| Ex | SQL> select * from emp where soundex(ename) = soundex(‘SMIT’);
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO ——- ——– —– —– ———— ——— ———- 7369 SMITH CLERK 7902 17-DEC-80 500 20
|
CONCAT(This will be used to combine two strings only) |
|
| Syntax | concat (string1, string2) |
| Ex | SQL> select concat(‘computer’,’ operator’) from dual;
CONCAT(‘COMPUTER’ ————————- computer operator If you want to combine more than two strings you have to use concatenation operator (||). SQL> select ‘how’ || ‘ are’ || ‘ you’ from dual; ‘HOW’||’ARE ————— how are you
|
ASCII(This will return the decimal representation in the database character set of the first character of the string.) |
|
| Syntax | ascii (string) |
| Ex | SQL> select ascii(‘a’), ascii(‘apple’) from dual;
ASCII(‘A’) ASCII(‘APPLE’) ———— —————— 97 97 |
CHR(This will return the character having the binary equivalent to the string in either the database character set or the national character set.) |
|
| Syntax | chr (number) |
| Ex | SQL> select chr(97) from dual;
CHR —– a |
SUBSTR(This will be used to extract substrings.) |
|
| Syntax | substr (string, start_chr_count [, no_of_chars]) |
| Ex | SQL> select substr(‘computer’,2), substr(‘computer’,2,5), s substr(‘computer’,3,7) from dual;
SUBSTR( SUBST SUBSTR ———- ——- ——– omputer omput mputer
1 If no_of_chars parameter is negative then it will display nothing. 2 If both parameters except string are null or zeros then it will display nothing. 3 If no_of_chars parameter is greater than the length of the string then it ignores and calculates based on the orginal string length. 4 If start_chr_count is negative then it will extract the substring from right end.
1 2 3 4 5 6 7 8
C O M P U T E R
-8 -7 -6 -5 -4 -3 -2 -1
|
INSTR(This will allows you for searching through a string for set of characters. ) |
|
| Syntax | This will allows you for searching through a string for set of characters.
This will allows you for searching through a string for set of characters. |
| Ex | SQL> select instr(‘information’,’o’,4,1), instr(‘information’,’o’,4,2) from dual;
INSTR(‘INFORMATION’,’O’,4,1) INSTR(‘INFORMATION’,’O’,4,2) ———————————- ————————————- 4 10 1 If you are not specifying start_chr_count and occurrence then it will start search from the beginning and finds first occurrence only. 2 If both parameters start_chr_count and occurrence are null, it will display nothing. |
DECODE(Decode will act as value by value substitution. For every value of field, it will checks for a match in a series of if/then tests. ) |
|
| Syntax | decode (value, if1, then1, if2, then2, ……. else); |
| Ex | SQL> select sal, decode(sal,500,’Low’,5000,’High’,’Medium’) from emp;
SAL DECODE —– ——— 500 Low 2500 Medium 2000 Medium 3500 Medium 3000 Medium 5000 High 4000 Medium 5000 High 1800 Medium 1200 Medium 2000 Medium 2700 Medium 2200 Medium 3200 Medium
SQL> select decode(1,1,3), decode(1,2,3,4,4,6) from dual;
DECODE(1,1,3) DECODE(1,2,3,4,4,6) —————– ———————— 3 6 1 If the number of parameters are odd and different then decode will display nothing. 2 If the number of parameters are even and different then decode will display last value. 3 If all the parameters are null then decode will display nothing. 4 If all the parameters are zeros then decode will display zero. |
GREATEST(This will give the greatest string.) |
|
| Syntax | greatest (strng1, string2, string3 … stringn) |
| Ex | SQL> select greatest(‘a’, ‘b’, ‘c’), greatest(‘satish’,’srinu’,’saketh’) from dual;
GREAT GREAT ——- ——- c srinu 1. If all the parameters are nulls then it will display nothing. 2. If any of the parameters is null it will display nothing. |
LEAST(This will give the least string.) |
|
| Syntax | greatest (strng1, string2, string3 … stringn) |
| Ex | SQL> select least(‘a’, ‘b’, ‘c’), least(‘satish’,’srinu’,’saketh’) from dual;
LEAST LEAST ——- ——- a saketh 1. If all the parameters are nulls then it will display nothing. 2. If any of the parameters is null it will display nothing. |
COALESCE(This will gives the first non-null string) |
|
| Syntax | coalesce (strng1, string2, string3 … stringn) |
| Ex | SQL> select coalesce(‘a’,’b’,’c’), coalesce(null,’a’,null,’b’) from dual;
COALESCE COALESCE ———– ———– a a |
Visit Today : 24
Visit Yesterday : 87
This Month : 233
This Year : 7793
Comments