SQL String Functions

SQL String Functions

Sql string function is a built-in string function.
It perform an operation on a string input value and return a string or numeric value.
Below is All built-in Sql string function :ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE,CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME,STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE,UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM


Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)SELECT ASCII('a') -- Value = 97
SELECT ASCII('b') -- Value = 98
SELECT ASCII('c') -- Value = 99
SELECT ASCII('A') -- Value = 65
SELECT ASCII('B') -- Value = 66
SELECT ASCII('C') -- Value = 67
SELECT ASCII('1') -- Value = 49
SELECT ASCII('2') -- Value = 50
SELECT ASCII('3') -- Value = 51
SELECT ASCII('4') -- Value = 52
SELECT ASCII('5') -- Value = 53 


Example SQL String Function - SPACE 
-Returns spaces in your SQL query (you can specific the size of space). 
Syntax - SPACE ( integer)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS 


Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] ) 
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial') 
-- Value = 27SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20) 
-- Value = 27SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)
-- Value = 0 (Because the index is count from 30 and above)


Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value = SQL Function


Example SQL String Function - QUOTENAME
-Returns a Unicode string with 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]


Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 ) 
SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value = SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value = Goodgoodning


Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer) 
SELECT LEFT('TravelYourself', 6) 
-- Value = Travel
SELECT LEFT('BeautyCentury',6) 
-- Value = Beauty


Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)-- Value = urself
SELECT RIGHT('BeautyCentury',6)-- Value = Century


Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)SELECT REPLICATE('Sql', 2) 
-- Value = SqlSql


Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING('SQLServer', 4, 3) 
-- Value = Ser


Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string) 
SELECT LEN('SQLServer')
-- Value = 9


Example SQL String Function - REVERSE
-Returns reverse a string.Syntax - REVERSE( string)SELECT REVERSE('SQLServer') 
-- Value = revreSLQS


Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char) 
SELECT UNICODE('SqlServer') 
-- Value = 83 (it take first character)
SELECT UNICODE('S')
-- Value = 83


Example SQL String Function - LOWER
-Convert string to lowercase.Syntax - LOWER( string )SELECT LOWER('SQLServer') 
-- Value = sqlserver


Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string ) 
SELECT UPPER('sqlserver') 
-- Value = SQLSERVER


Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )SELECT LTRIM(' sqlserver')-- Value = 'sqlserver' (Remove left side space or blanks)


Example SQL String Function - RTRIM 
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )SELECT RTRIM('SqlServer ')
-- Value = 'SqlServer' (Remove right side space or blanks)