STRING FUNCTIONS :
(A) UPPER FUNCTION :
- This function converts a string into upper case.
Syntax :-
UPPER(STRING)
Example :- Select upper(‘hello’)
Select empno,upper(ename) from emp.
(B) LOWER FUNCTION :-
- It is converts a string into lower case.
Syntax :-
Lower(string)
Example :-Select lower (‘hello’)
(C) LEN FUNCTION :-
- It returns string length.
Syntax :- len (string)
Example :- Select * from emp where Len(ename)=5.
(D) LEFT FUNCTION :-
- It returns specify no of character starting from leftside.
Syntax :-
Left (string,no of characters)
Example :- Select left(“hello”,4)
O/P -> hell
Select * from emp where left(ename,1)=’s’
(E) RIGHT FUNCTION :-
- To extract specify no of character starting from right side.
Syntax :-
Right(string,no of characters)
Example :-
Select right (“hello”,4)
O/P :–ello.
Update emp set
Ename=upper (left(ename,1))+lower(right(ename,len(ename-1)))
Output : -> Ename
Arun
Giri
Raju
Hari
(F) Substring FUNCTION:-
- Used to extract part of the string starting from specified character.
Syntax :- Substring (string,start,no of chars)
example :- select substring (‘hello’, 2, 3) delete from emp Where substring
(G) Charindex FUNCTION:-
- This function checks occurance of one string in another string.
Syntax :
Charindex (str1,str2,[start])
Str1 exists in str2 -> position
Not exists -> 0
Example :-Select charindex(‘0’,’hello welcome’)
O/P :-> 5 {by default first occurance exists}
Select charindex (‘0’,’hello welcome’,6)
O/P : 11
Ename :
Sachin r tendulkar
Mahendra s dhoni
Select left(ename,charindex(‘’,ename)-1) From emp
Output :- sachin
Mahendra
(H) Replace Function:
- This function is used to replace one string with another string.
Syntax :- replace(string,search string,replace with string)
Example :- Select replace(‘hello’,’ell’,’abc’)
O/P :-habco
Display employee records whose name with 2A’s
Example :–adams
Select * from emp Where len(ename) – len (replace(ename,’a’,’’))
5 3
=2 -> [5-3=2]
(I) SPACE Function :-
- This function is used to generate “spaces”.
Example :-
Select ‘hello’+space(5) + welcome
O/P → hello welcome