SQL SERVER – Build-In SQL String Functions With Examples

Here we will see some important SQL String Build-in Function of SQL Server. SQL String function can perform on the String where we gives the string input value and can return a string or numeric value. Using String Function SQL Expert or Beginners can get good command on SQL String Function. These functions are very easy in use.

ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME, STUFF, LEFT, LTRIM, RTRIM, REPLICATE, REVERSE, UNICODE, LOWER, RIGHT, UPPER, SUBSTRING, LEN.


1) ASCII
Explanation:- This Function Returns the ASCII code value of a keyboard button
Syntax - ASCII (character)

SELECT ASCII ('b') -- Value = 98

SELECT ASCII ('D') -- Value = 68

SELECT ASCII ('1') -- Value = 49

SELECT ASCII ('2') -- Value = 50

SELECT ASCII ('@') -- Value = 64

 2) SPACE
Explanation: - This Function returns the spaces in your SQL query (you can specific the size of space).
Syntax - SPACE (integer)

 SELECT ('SQL') + SPACE(0) + ('SERVER')

-- Value = SQLSERVER

SELECT ('SQL') + SPACE(1) + ('SERVER')

-- Value = SQL SERVER

SELECT ('SQL') + SPACE(5) + ('SERVER')

-- Value = SQL     SERVER 

3) CHARINDEX
Explanation:- This Function Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] )
SELECT CHARINDEX('nice', 'I think this is the nice blog for sql')

-- Value = 21

SELECT CHARINDEX('nice', 'I think this is the nice blog for sql', 20)

-- Value = 21

SELECT CHARINDEX('nice', 'I think this is the nice blog for sql', 30)

-- Value = 0 (Because the index is count from 30 and above)

4) - REPLACE
Explanation: - This Function Returns Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )

 SELECT REPLACE('MY NAME ARE JAINENDRA' , 'ARE', 'IS')

-- Value = MY NAME IS JAINENDRA

 5) - QUOTENAME
Explanation: - This Function Returns a Unicode string and also includes the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME (‘string’ [ , 'quote_character' ] )
 

 SELECT QUOTENAME('Sql[]String')

-- Value = [Sql[]]String]

SELECT QUOTENAME('SqlString')

-- Value = [SqlString]

 6) - STUFF
Explanation: - This Function Returns a  Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF (string1 , startindex , length , string2 )

SELECT STUFF('Sqlserver', 4, 6, '2005')

-- Value = Sql2005

SELECT STUFF('SqlFuntion', 4, 8, '2008')

-- Value = Sql2008

SELECT STUFF('SqlTable', 4, 5, '2012')

-- Value = Sql2012
 

7) - LEFT
Explanation: - This Function returns the left part of a string with the specified number of characters.

Syntax - LIGHT( string , integer)
 

 SELECT LEFT('Musicworld', 5)

-- Value = Music

SELECT LEFT('Loveyou',4)

-- Value = Love


 8) - RIGHT
Explanation:- This Function returns the right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)

SELECT RIGHT('MusicWorld', 5)

-- Value = World

SELECT RIGHT('Loveyou',3)

-- Value = you

 9) - LTRIM
Explanation: - This Function returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )
 

 SELECT LTRIM('            SQLSERVER2012')

-- Value = SQLSERVER2012

10) - RTRIM
Explanation: - This Function returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )
 

SELECT RTRIM('SQLSERVER2012        ')

-- Value = SQLSERVER2012

11) - REPLICATE
Explanation: - This Function repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)
 

SELECT REPLICATE('Hello', 2)

-- Value = HelloHello

SELECT REPLICATE('Hello', 3)

-- Value = HelloHelloHello


12) - REVERSE
Explanation: - This Function returns the reverse a string.
Syntax - REVERSE( string)
 

 SELECT REVERSE('SQL-INTEGRITY')

-- Value = YTIRGETNI-LQS

13) - UNICODE
Explanation: - This Function returns the Unicode standard integer value.
Syntax - UNICODE( char)
 

 SELECT UNICODE('GOODMORNING')

-- Value = 71

SELECT UNICODE('GOOD')

-- Value = 71

SELECT UNICODE('G')

-- Value = 71

--Return only first character value

14) - LOWER
Explanation: - This Function converts string to lowercase.
Syntax - LOWER( string )
 

SELECT LOWER('SqlServer2008')

-- Value = sqlserver2008


 15) - UPPER
Explanation: - This Function converts string to Uppercase.
Syntax - UPPER( string )
 

SELECT UPPER('SqlServer2008')

-- Value = SQLSERVER2008

16) - SUBSTRING
Explanation: - This Function returns the part of a string.
Syntax - SUBSTRING ( string, startindex , length )

SELECT SUBSTRING('SQL-Server-2008', 4, 8)

-- Value = -Server-

17) - LEN
Explanation: - This Function returns the number of characters in a string.
Syntax - LEN( string)
 

SELECT LEN('Get My Length')

-- Value = 13