String functions perform an operation on 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.
EX: SELECT 'V' AS CHARACTER , ASCII('V') AS ASCIIValue
O/p: Character ASCIIValue
----------------------
D 86
2) 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 thatvalue
EX: SELECT 248 AS UniCodeValue ,NChar(248) AS UniCodeCharacter
O/p: UnicodeValue UnicodeCharacter
------------------------------------------------------
248 ΓΈ
4) CHARINDEX (expression1, expression2 [ , start_location ] ): It searches expression2 forexpression1 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
O/p: Location
------------------
10
5) 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
-------------------------------------------------
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.
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.
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.
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.
EX: SELECT LEN(' Vijaya Lakshmi') AS Length
O/p : Length
------------------
15
10) LOWER (character_expression ): Returns a character expression after converting uppercase character data to lowercase.
EX: SELECT LOWER('ViShAl JhArWaDe') AS LowerCase
O/p : LowerCase
---------------
vishal jharwade
11) UPPER (character_expression ): Returns a character expression with lowercase character data converted to uppercase.
EX: SELECT UPPER(' ViShAl JhArWaDe') AS UpperCase
O/p : 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.
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') AS COLUMN1
O/p: Column1
-----------
41
SELECT PATINDEX ('%hate%', 'I love reading history books but do not like Geography
books') AS COLUMN1
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.
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.
EX: SELECT REPLACE ('Money and Tide wait for 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.
EX: SELECT REPLICATE ('*', 8) + 'End of the Document' + REPLICATE ('*', 8) AS COLUMN1
O/p : COLUMN1
--------------------------
********End of the Document********
16) REVERSE ( string_expression ): Returns the reverse of a string value.
EX: SELECT REVERSE(12345678910) AS Reversed
O/p : Reversed
------------------
01987654321
EX: SELECT REVERSE('NITIN') AS Reversed
O/p : Reversed
------------
NITIN
EX: SELECT REVERSE('VISHAL') AS Reversed
O/p : Reversed
-----------------
LAHSIV
17) LTRIM (character_expression): Returns a character expression after it removes 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.
EX: SELECT RTRIM('SQLServer ') AS COLUMN1
O/p : COLUMN1
-------------
SQLServer
19) SPACE (integer_expression ): Returns a string of repeated 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.
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.
EX: SELECT STUFF ('SQL Server is a File database', 17,4, 'Relational') AS COLUMN1
O/p : AS COLUMN1
-----------------
SQL Server is a Relational database
22) SUBSTRING (value_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) ASStringOutput
O/p : StringOutput
---------------------
Data Mining
23) UNICODE ( 'ncharacter_expression' ): Returns the integer value, as defined
by the Unicode standard, for the first character of the input 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.
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @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
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.
EX: SELECT CONCAT ( 'SQL ', 'Server ', 2012, ' is the latest version') AS Result;
O/p: Result
SQL Server 2012 is the latest version
No comments:
Post a Comment