String functions perform an operation on
an input string and return a string or numeric value:
an input string and return a string or numeric value:
1)
ASCII (character_expression): Returns the
integer value, as defined by the ASCII standard, for the first character of the
input expression.
ASCII (character_expression): Returns the
integer value, as defined by the ASCII standard, for the first character of the
input expression.
EX: SELECT ‘V’ AS CHARACTER , ASCII(‘V’) AS ASCIIValue
O/p: Character ASCIIValue
———————-
D
86
86
2)
CHAR (
integer_expression):It takes ASCII Value and returns ASCII character for that value
CHAR (
integer_expression):It takes ASCII Value and returns ASCII character for that value
EX: SELECT 86 AS ASCIIValue ,Char(86) AS CHARACTER
O/p: ASCIIValue Character
—————————–
86 V
3)
NCHAR ( integer_expression ):It takes UNICODE Value and returns UNICODE character for
that value
NCHAR ( integer_expression ):It takes UNICODE Value and returns UNICODE character for
that value
EX: SELECT 248 AS UniCodeValue ,NChar(248) AS UniCodeCharacter
O/p: UnicodeValue UnicodeCharacter
——————————————————
248 ø
4)
CHARINDEX (expression1,
expression2 [ , start_location ] ): It searches expression2
for expression1 and
returns its starting position if found. The search starts at start_location.
CHARINDEX (expression1,
expression2 [ , start_location ] ): It searches expression2
for expression1 and
returns its starting position if found. The search starts at start_location.
EX: SELECT CHARINDEX (‘Tide’, ‘Time and Tide wait for
none’) AS Location
none’) AS Location
O/p: Location
——————
10
5)
SOUNDEX (character_expression): Returns a four-character (SOUNDEX) code to evaluate
the similarity of two strings.
SOUNDEX (character_expression): Returns a four-character (SOUNDEX) code to evaluate
the similarity of two strings.
EX: SELECT SOUNDEX (‘Jeanie’) AS COLUMN1, SOUNDEX (‘Jeany’) AS COLUMN2
O/p
: COLUMN1 COLUMN2
: COLUMN1 COLUMN2
————————————————-
S500 S500
6)
DIFFERENCE (character_expression
, character_expression ): Returns
an integer value (0
to 4) that indicates the difference between the SOUNDEX values of two character expressions 0
means less similarity. 4 mean strong
similarity.
DIFFERENCE (character_expression
, character_expression ): Returns
an integer value (0
to 4) that indicates the difference between the SOUNDEX values of two character expressions 0
means less similarity. 4 mean strong
similarity.
EX: SELECT DIFFERENCE(‘Green’,‘Greene’) AS COLUMN1;
O/p: COLUMN1
——————
4
7)
LEFT (character_expression ,
integer_expression ): Returns
the left part of a character string with the
specified number of characters.
LEFT (character_expression ,
integer_expression ): Returns
the left part of a character string with the
specified number of characters.
EX: SELECT LEFT(‘Anubhi’,3)
O/p: NoColumnValue
——————
Anu
8)
RIGHT (character_expression ,
integer_expression ): Returns
the right part of a character string with the
specified number of characters.
RIGHT (character_expression ,
integer_expression ): Returns
the right part of a character string with the
specified number of characters.
EX: SELECT RIGHT(‘SQLServer’,6)
O/p: NoColumnValue
——————
Server
9)
LEN (string_expression ): Returns the number of characters of the specified
string expression, excluding trailing blanks.
LEN (string_expression ): Returns the number of characters of the specified
string expression, excluding trailing blanks.
EX: SELECT LEN(‘ Vijaya Lakshmi’) AS Length
O/p
: Length
: Length
——————
15
10) LOWER (character_expression ): Returns a character expression after converting
uppercase character data to lowercase.
uppercase character data to lowercase.
EX: SELECT LOWER(‘ViShAl JhArWaDe’) AS LowerCase
O/p
: LowerCase
: LowerCase
—————
vishal jharwade
11) UPPER (character_expression
): Returns a character
expression with lowercase character data converted to uppercase.
): Returns a character
expression with lowercase character data converted to uppercase.
EX: SELECT UPPER(‘ ViShAl JhArWaDe’) AS UpperCase
O/p :
UpperCase
UpperCase
————————
VISHAL JHARWADE
12) PATINDEX (
‘%pattern%’ , expression ): Returns
the starting position of the first occurrence of a pattern in a specified expression, or
zeros if the pattern is not found, on all valid text and character data types.
‘%pattern%’ Is a literal string. Wildcard
characters can be used; however, the % character must come before and follow pattern is an expression of the
character string data type category.
‘%pattern%’ , expression ): Returns
the starting position of the first occurrence of a pattern in a specified expression, or
zeros if the pattern is not found, on all valid text and character data types.
‘%pattern%’ Is a literal string. Wildcard
characters can be used; however, the % character must come before and follow pattern is an expression of the
character string data type category.
EX: SELECT PATINDEX (‘%like%’,‘ I like reading history books but do not like Geography
books‘) AS
COLUMN1
books‘) AS
COLUMN1
O/p: Column1
———–
3
SELECT PATINDEX (‘%like%’, ‘I love reading history
books but do not like Geography
books’) AS COLUMN1
books but do not like Geography
books’) AS COLUMN1
O/p: Column1
———–
41
SELECT PATINDEX (‘%hate%’, ‘I love reading history
books but do not like Geography
books’) AS COLUMN1
books but do not like Geography
books’) AS COLUMN1
O/p: Column1
———
0
13) QUOTENAME (‘character_string’
[ , ‘quote_character’ ] ): Returns
a Unicode string with the delimiters
added to make the input string a valid Microsoft SQL Server delimited
identifier.
[ , ‘quote_character’ ] ): Returns
a Unicode string with the delimiters
added to make the input string a valid Microsoft SQL Server delimited
identifier.
EX: SELECT QUOTENAME(‘abc[]def’) AS COLUMN1
O/p : COLUMN1
————————
[abc[]]defg]
14) REPLACE (string_expression, string_pattern ,
string_replacement ): Replaces all occurrences of a specified string value
with another string value.
string_replacement ): Replaces all occurrences of a specified string value
with another string value.
EX: SELECT REPLACE (‘Money and Tide wait for
none.’,‘Money’,‘Time’) AS COLUMN1;
none.’,‘Money’,‘Time’) AS COLUMN1;
O/p: COLUMN1
———-
Time and Tide wait for none.
15) REPLICATE (string_expression,
integer_expression):
Repeats a string value a specified number of times.
integer_expression):
Repeats a string value a specified number of times.
EX: SELECT REPLICATE (‘*’, 8) + ‘End of the Document’ +
REPLICATE (‘*’, 8) AS COLUMN1
REPLICATE (‘*’, 8) AS COLUMN1
O/p
: COLUMN1
: COLUMN1
————————–
********End of the Document********
16)
REVERSE ( string_expression ): Returns the reverse of a string
value.
REVERSE ( string_expression ): Returns the reverse of a string
value.
EX: SELECT REVERSE(12345678910) AS Reversed
O/p
: Reversed
: Reversed
——————
01987654321
EX: SELECT REVERSE(‘NITIN’) AS Reversed
O/p
: Reversed
: Reversed
————
NITIN
EX: SELECT REVERSE(‘VISHAL’) AS Reversed
O/p
: Reversed
: Reversed
—————–
LAHSIV
17) LTRIM (character_expression): Returns a character expression after it removes
leading blanks.
leading blanks.
EX: SELECT LTRIM(‘ SQLServer’) AS COLUMN1
O/p: COLUMN1
—————
SQLServer
18) RTRIM (
character_expression ): Returns
a character string after truncating all trailing blanks.
character_expression ): Returns
a character string after truncating all trailing blanks.
EX: SELECT RTRIM(‘SQLServer ‘) AS COLUMN1
O/p
: COLUMN1
: COLUMN1
————-
SQLServer
19) SPACE (integer_expression ): Returns a string of repeated
spaces.
spaces.
EX: SELECT ‘SQL’ + SPACE(5) + ‘Server’ AS COLUMN1
O/p : COLUMN1
————
SQL Server
20) STR ( float
expression [ , length [ , decimal ] ] ): Returns character data converted from numeric data.
expression [ , length [ , decimal ] ] ): Returns character data converted from numeric data.
EX: SELECT STR(123.45, 6, 1)
O/p : NoColumnValue
——-
123.5
21) STUFF ( character_expression , start , length
,character_expression ): The
STUFF function inserts a string into another string. It deletes a specified
length of characters in the first string at the start position and then inserts
the second string into the first string at the start position.
,character_expression ): The
STUFF function inserts a string into another string. It deletes a specified
length of characters in the first string at the start position and then inserts
the second string into the first string at the start position.
EX: SELECT STUFF (‘SQL Server is a File database’, 17,4,
‘Relational’) AS COLUMN1
‘Relational’) AS COLUMN1
O/p : AS COLUMN1
—————–
SQL Server is a
Relational database
Relational database
22) SUBSTRING (value_expression
,start_expression , length_expression ): Returns part of a character, binary, text, or image expression.
,start_expression , length_expression ): Returns part of a character, binary, text, or image expression.
EX: SELECT SUBSTRING(‘Data Mining is knowledge discovery in Databases’,1, 11) AS StringOutput
O/p : StringOutput
———————
Data Mining
23) UNICODE (
‘ncharacter_expression’ ): Returns
the integer value, as defined
‘ncharacter_expression’ ): Returns
the integer value, as defined
by the Unicode standard, for the first character of the input
expression.
expression.
EX: SELECT UNICODE(‘a’) AS COLUMN0,UNICODE(‘A’) AS COLUMN1,UNICODE(‘0’)
O/p:
COLUMN0 COLUMN1 COLUMN2
97 65 48
24) FORMAT (value, format [, culture ] ): Returns a value formatted with the specified format. Culture is optional
in this function.
in this function.
DECLARE @d DATETIME
= GETDATE();
= GETDATE();
SELECT FORMAT(
@d, ‘dd/mm/yyyy’) AS ‘DateTime Result’
@d, ‘dd/mm/yyyy’) AS ‘DateTime Result’
,FORMAT(123456789,‘###-##-####’) AS ‘Custom Number Result’
,FORMAT(@d,‘d’) AS Date1
,FORMAT(@d,‘D’) AS Date2;
DateTime Result Custom Number Result
Date1 Date2
Date1 Date2
29/25/2012 123-45-6789 10/29/2012 Monday, October 29, 2012
25) CONCAT (string_value1, string_value2 [,
string_valueN ] ) : Returns a string that is
the result of concatenating two or more string values.
string_valueN ] ) : Returns a string that is
the result of concatenating two or more string values.
EX: SELECT CONCAT ( ‘SQL ‘, ‘Server ‘, 2012, ‘ is the latest version’)
AS Result;
AS Result;
O/p: Result
SQL Server 2012 is the latest version