Tuesday, February 3, 2015

String Functions in Sql Server

1:49 AM - By ajay desai 0

(i)  ASCII(s): - this function returns the ASCII code value of a character.
For ex: - select ASCII('a')

(ii)  CHAR(n): - this function returns the character of an ASCII code value.
For ex: - select CHAR(97)
             

(iii)   NCHAR(n): - this function returns a Unicode character of a given integer value.
For ex: - select NCHAR(500)
                         

(iv)     CHARINDEX(expToFind,expToSearch[,start_location]): - this function searches for an expression within another expression and returns its starting index position if found.
For ex: - select charindex('o','hello')
     
         

select charindex('o','hello world',7)

         

create table employee(empid int constraint pk primary key,ename varchar(50))

Retreive the details of employees whose name starts with ‘v’
select *from employee where CHARINDEX('v',ename)>0

             

(v)  LEN(s): - this function returns length of a string (group of characters). Where length specifies the no: of characters in a string excluding trailing spaces (i.e empty space after a string).

For ex: - select LEN('hello ')

               
 
(vi) LEFT: - this function returns the left part of a given string having specified                     no: of characters.
                    For ex:- select *From employee where LEFT(ename,2)='ab'
               
                  

                This query retrieves all the details of those employees’ whose name starts                        with ‘ab’ from left part of their name string.

(vii)  RIGHT: - this function returns the right part of a given string having                               specified no: of characters.
                For ex: - select *from employee where RIGHT(ename,3)='jay'
                         
      The above given query retrieves all the details of those employees’ whose name              ends with ‘jay’ from the right part of their name string.                                   

(viii) UPPER: - This function converts a specific column values to uppercase.

select UPPER(ename) from employee

                                                                                                    
(ix)    LOWER: - this function converts a specific column values to lowercase.

select LOWER(ename) from employee

                                                                                                 

(x) LTRIM(s): - This function returns an expression after it removes leading blanks.

select LEN(LTRIM('   hello'))

                                                                                         

(xi) RTRIM(s): - This function returns an expression after removing trailing blanks.


select LEN(RTRIM('hello   '))

 
   (xii) REPLACE(s1,s2,s3): - this function replaces all occurrences of the string s2 in string s1 with string s3.
                          
                 For ex: - select REPLACE('helloworld','l','x')
          
                   
     

 (xiii) REPLICATE(s,n): - this function repeats a string value for specified number of times.
For ex: - select REPLICATE('hello',2)

  

(xiv)        REVERSE(s): - this function returns reverse order of a string value.
For ex: - select reverse('ajay')

                          

Tags:
About the Author

I am Azeheruddin Khan having more than 6 year experience in c#, Asp.net and ms sql.My work comprise of medium and enterprise level projects using asp.net and other Microsoft .net technologies. Please feel free to contact me for any queries via posting comments on my blog,i will try to reply as early as possible. Follow me @fresher2programmer
View all posts by admin →

Get Updates

Subscribe to our e-mail newsletter to receive updates.

Share This Post

0 comments:

adsense

© 2014 Fresher2Programmer. WP Theme-junkie converted by Bloggertheme9
Powered by Blogger.
back to top