String Functions in SQL Server – SQL Server Tutorial

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

Leave a Reply

Your email address will not be published. Required fields are marked *