SQL Server String Functions

0 Condivisioni

200px Sql server logo - SQL Server 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 FunctionASCII
– 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 FunctionSPACE
-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 FunctionCHARINDEX
-Returns the starting position of a character string.
Syntax – CHARINDEX ( string1, string2 [ , start_location ] )
SELECT CHARINDEX(‘SQL’, ‘Well organized understand SQL tutorial’)

— Value = 27
SELECT CHARINDEX(‘SQL’, ‘Well organized understand SQL tutorial’, 20)

— Value = 27
SELECT CHARINDEX(‘SQL’, ‘Well organized understand SQL tutorial’, 30)

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

Example SQL String FunctionREPLACE
-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 FunctionQUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
SyntaxQUOTENAME ( ‘string’ [ , ‘quote_character’ ] )
SELECT QUOTENAME(‘Sql[]String’)
— Value = [Sql[]]String]

Example SQL String FunctionSTUFF
– Deletes a specified length of characters and inserts string at a specified starting index.
SyntaxSTUFF ( string1 , startindex , length , string2 )

SELECT STUFF(‘SqlTutorial’, 4, 6, ‘Function’)
— Value = SqlFunctional
SELECT STUFF(‘GoodMorning’, 5, 3, ‘good’)
— Value = Goodgoodning

Example SQL String FunctionLEFT
-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 FunctionRIGHT
-Returns right part of a string with the specified number of characters.
SyntaxRIGHT( string , integer)
SELECT RIGHT(‘TravelYourself’, 6)
— Value = urself
SELECT RIGHT(‘BeautyCentury’,6)
— Value =
Century

Example SQL String FunctionREPLICATE
-Repeats string for a specified number of times.

SyntaxREPLICATE (string, integer)
SELECT REPLICATE(‘Sql’, 2)

— Value = SqlSql

Example SQL String FunctionSUBSTRING
-Returns part of a string.

SyntaxSUBSTRING ( string, startindex , length )
SELECT SUBSTRING(‘SQLServer’, 4, 3)

— Value = Ser

Example SQL String FunctionLEN
-Returns number of characters in a string.
SyntaxLEN( string)
SELECT LEN(‘SQLServer’)
— Value =
9

Example SQL String FunctionREVERSE
-Returns reverse a string.
SyntaxREVERSE( string)
SELECT REVERSE(‘SQLServer’)

— Value = revreSLQS

Example SQL String FunctionUNICODE
-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 FunctionLOWER
-Convert string to lowercase.
SyntaxLOWER( string )
SELECT LOWER(‘SQLServer’)

— Value = sqlserver

Example SQL String FunctionUPPER
-Convert string to Uppercase.
SyntaxUPPER( string )
SELECT UPPER(‘sqlserver’)
— Value = SQLSERVER

Example SQL String FunctionLTRIM
-Returns a string after removing leading blanks on Left side.
SyntaxLTRIM( string )
SELECT LTRIM(‘ sqlserver’)
— Value = ‘sqlserver’ (Remove left side space or blanks)

Example SQL String FunctionRTRIM
-Returns a string after removing leading blanks on Right side.

SyntaxRTRIM( string )
SELECT RTRIM(‘SqlServer ‘)

— Value = ‘SqlServer’ (Remove right side space or blanks)


Ciao Fab


0 Condivisioni

Fabrizio Cannatelli

Fondatore di Informarea.it, è un appassionato di informatica. Ha lavorato per molti anni come Analista Programmatore presso varie aziende utilizzando diversi linguaggi di sviluppo, oggi svolge un lavoro completamente diverso ma la voglia di comunicare e di condividere con il web i suoi studi e le sue curiosità lo hanno spinto a far nascere questo blog non solo per esprimere e mostrare la passione per questo mondo, ma anche per confrontarsi con nuove esperienze di sviluppo.

Fabrizio Cannatelli

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.