Character functions
accept “character data or numaric data” as argument and returns “character” or “number”
values.
1.
Oracle
treats whenever there is special character/space as a word.
2.
Oracle
doesn’t treated whenever there is number as a word.
The following
are some of the character functions in sql,
1.
Lower(arg)
2.
Upper(arg)
3.
Initcap(arg)
4.
Concat(arg1,arg2)
5.
Length(arg)
6.
Replace(arg,str1,str2)
7.
Substr(arg,pos,n)
8.
Ltrim(arg)
9.
Rtrim(arg)
10. Lapd(arg,n,ch)
11. Rpad(arg,n,ch)
12. Instr(arg,pattern,occurrence)
1.lower(): converts all chars in a string into lowercase.
SYNTAX: lower(args);
Here args may be any datatype.
EX: SQL>
select lower('oracle') from dual;
LOWER(
------oracle
SQL>
select lower('ORACLE') from dual;
LOWER(
------oracle
SQL>
select lower('ORaclE') from dual;
LOWER(
------oracle
SQL>
select lower(10) from dual;
LO
--10
SQL>
select lower() from dual;
select
lower() from dual
*
ERROR at
line 1:
ORA-00909:
invalid number of arguments
SQL>
select lower(' ') from dual;
L
SQL>
select lower(null) from dual;
L
-
SQL>
select lower('@#%^') from dual;
LOWE
----@#%^
SQL>
select lower(ename) from emp;
LOWER(ENAME)
--------------------
ramu
raju
adams
king
scott
2.upper(): converts all characters in string into uppercase
SYNTAX: upper(args);
Here args may be any
datatype.
EX: SQL> select upper(ename) from emp;
UPPER(ENAME)
--------------------RAMU
RAJU
ADAMS
KING
SCOTT
SQL>
select upper('oracle') from emp;
UPPER(
------ORACLE
ORACLE
ORACLE
ORACLE
ORACLE
SQL>
select upper('oracle') from dual;
UPPER(
------ORACLE
SQL>
select upper('ORACLE') from dual;
UPPER(
------ORACLE
SQL>
select upper('OraClE') from dual;
UPPER(
------ORACLE
SQL>
select upper('10') from dual;
UP
--10
SQL>
select upper('@$%#') from dual;
UPPE
----@$%#
SQL>
select upper('') from dual;
U
-
SQL>
select upper(null) from dual;
U
-
3.length():Returns the number of characters in a text string.
SYNTAX: length(args);
Here args may be any
datatype.
EX: SQL> select length(ename) from
emp;
LENGTH(ENAME)
-------------
4
4
5
4
5
SQL>
select length('varaprasad') from dual;
LENGTH('VARAPRASAD')
--------------------
10
SQL>
select length('it returns length of char in characters') from dual;
LENGTH('ITRETURNSLENGTHOFCHARINCHARACTERS')
-------------------------------------------
39
SQL>
select length('') from dual;
LENGTH('')
----------
SQL>
select length(' ') from dual;
LENGTH('')
---------- 1
SQL>
select length(null) from dual;
LENGTH(NULL)
------------
SQL>
select length(23882) from dual;
LENGTH(23882)
-------------
5
SQL>
select length(2 3882) from dual;
select
length(2 3882) from dual
*
ERROR at
line 1:
ORA-00907:
missing right parenthesis
SQL>
select length(2' ' 3882) from dual;
select
length(2' ' 3882) from dual
*
ERROR at
line 1:
ORA-00907:
missing right parenthesis
SQL>
select length(23882,57389) from dual;
select
length(23882,57389) from dual
*
ERROR at
line 1:
ORA-00909:
invalid number of arguments
SQL>
select char(1) from dual;
select
char(1) from dual
*
ERROR at
line 1:
ORA-00936:
missing expression
4.initcap(): returns first character in each word of string into capital letter.
SYNTEX: initcap(args);
Here args may be any datatype.
EX:
SQL>
select initcap('this is a intialcap') fro dual;
select
initcap('this is a intialcap') fro dual
*
ERROR at
line 1:
ORA-00923:
FROM keyword not found where expected
SQL>
select initcap('this is a intialcap') from dual;
INITCAP('THISISAINT
-------------------
This Is A
Intialcap
SQL>
select initcap('this is a intial cap') from dual;
INITCAP('THISISAINTI
--------------------
This Is A
Intial Cap
SQL>
select initcap('his is a intial cap') from dual;
INITCAP('HISISAINTI
-------------------
His Is A
Intial Cap
SQL>
select initcap(1225236) from dual;
INITCAP
-------1225236
SQL>
select initcap('@$%') from dual;
INI
---@$%
SQL>
select initcap(null) from dual;
I
-
SQL>
select initcap(ename) from emp;
INITCAP(ENAME)
--------------------Ramu
Raju
Adams
King
Scott
5.concat(): The CONCATENATE function joins two text strings into one text string. The joined items can be text, numbers or Boolean values represented as text, or a combination of those items. You can also use a column reference if the column contains appropriate values.
SYNTAX: concat (arg1,arg2);
EX: SQL> select
concat('scott','tiger') from dual;
CONCAT('SC
----------scotttiger
SQL>
select concat('scott',123) from dual;
CONCAT('
--------scott123
SQL>
select concat('scott',) from dual;
select
concat('scott',) from dual
*
ERROR at
line 1:
ORA-00936:
missing expression
SQL>
select concat('scott','_tiger') from dual;
CONCAT('SCO
-----------scott_tiger
SQL>
select concat('scott_','tiger') from dual;
CONCAT('SCO
-----------scott_tiger
SQL>
select concat(concat('scott','_'),'tiger') from dual;
CONCAT(CONC
-----------scott_tiger
SQL>
select concat(concat(' ','_'),'tiger') from dual;
CONCAT(
-------
_tiger
SQL>
select concat(concat('scott','_'),'') from dual;
CONCAT
------scott_
SQL>
select concat(concat(null,'_'),'') from dual;
C
-
_
SQL>
select concat(concat(null,'_'),'tiger') from dual;
CONCAT
------_tiger
SQL>
select concat(concat('scott','_'),null) from dual;
CONCAT
------scott_
SQL>
select concat(ename,dept) from emp;
CONCAT(ENAME,DEPT)
------------------------------
ramuece
rajucse
adamseee
kingece
scottece
SQL>
select concat(concat(ename,'-'),dept) from emp;
CONCAT(CONCAT(ENAME,'-'),DEPT)
-------------------------------
ramu-ece
raju-cse
adams-eee
king-ece
scott-ece
*concat
within a concat called nested concat.
No comments:
Post a Comment