| Function |
Description |
Sql |
| ASCII |
Returns the ASCII value for the specific character |
SELECT ASCII(CustomerName) AS NumCodeOfFirstChar
FROM Customers; |
| CHAR |
Returns the character based on the ASCII code |
SELECT CHAR(65) AS CodeToCharacter; |
| CHARINDEX |
Returns the position of a substring in a string |
SELECT CHARINDEX('t', 'Customer') AS MatchPosition; |
| CONCAT |
Adds two or more strings together |
SELECT CONCAT('W3Schools', '.com'); |
| Concat with + |
Adds two or more strings together |
SELECT 'W3Schools' + '.com'; |
| CONCAT_WS |
Adds two or more strings together with a separator |
SELECT CONCAT_WS('.', 'www', 'W3Schools', 'com'); |
| DATALENGTH |
Returns the number of bytes used to represent an expression |
SELECT DATALENGTH('W3Schools.com'); |
| DIFFERENCE |
Compares two SOUNDEX values, and returns an integer value |
SELECT DIFFERENCE('Juice', 'Jucy'); |
| FORMAT |
Formats a value with the specified format |
DECLARE @d DATETIME = '12/01/2018';
SELECT FORMAT (@d, 'd', 'en-US') AS 'US English Result',
FORMAT (@d, 'd', 'no') AS 'Norwegian Result',
FORMAT (@d, 'd', 'zu') AS 'Zulu Result',
SELECT FORMAT(123456789, '##-##-#####');
|
| LEFT |
Extracts a number of characters from a string (starting from left) |
SELECT LEFT('SQL Tutorial', 3) AS ExtractString;
|
| LEN |
Returns the length of a string |
SELECT LEN('W3Schools.com') |
| LOWER |
Converts a string to lower-case |
SELECT LOWER('SQL Tutorial is FUN!'); |
| LTRIM |
Removes leading spaces from a string |
SELECT LTRIM(' SQL Tutorial') AS LeftTrimmedString; |
| NCHAR |
SELECT NCHAR(65) AS NumberCodeToUnicode; |
SELECT LTRIM(' SQL Tutorial') AS LeftTrimmedString; |
| PATINDEX |
Returns the position of a pattern in a string |
SELECT PATINDEX('%schools%', 'W3Schools.com'); |
| QUOTENAME |
Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier |
SELECT QUOTENAME('abcdef'); |
| REPLACE |
Replaces all occurrences of a substring within a string, with a new substring |
SELECT REPLACE('SQL Tutorial', 'T', 'M'); |
| REPLICATE |
Repeats a string a specified number of times |
SELECT REPLICATE('SQL Tutorial', 5); |
| REVERSE |
Reverses a string and returns the result |
SELECT REVERSE('SQL Tutorial'); |
| RIGHT |
Extracts a number of characters from a string (starting from right) |
SELECT RIGHT('SQL Tutorial', 3) AS ExtractString; |
| RTRIM |
Removes trailing spaces from a string |
SELECT RTRIM('SQL Tutorial ') AS RightTrimmedString; |
| SOUNDEX |
Returns a four-character code to evaluate the similarity of two strings |
SELECT SOUNDEX('Juice'), SOUNDEX('Jucy'); |
| SPACE |
Returns a string of the specified number of space characters |
SELECT SPACE(10); |
| STR |
Returns a number as string |
SELECT STR(185); |
| STUFF |
Deletes a part of a string and then inserts another part into the string, starting at a specified position |
SELECT STUFF('SQL Tutorial', 1, 3, 'HTML'); |
| SUBSTRING |
Extracts some characters from a string |
SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString; |
| TRANSLATE |
Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument. |
SELECT TRANSLATE('Monday', 'Monday', 'Sunday'); // Results in Sunday |
| TRIM |
Removes leading and trailing spaces (or other specified characters) from a string |
SELECT TRIM(' SQL Tutorial! ') AS TrimmedString; |
| UNICODE |
Returns the Unicode value for the first character of the input expression |
SELECT UNICODE('Atlanta'); |
| UPPER |
Converts a string to upper-case |
SELECT UPPER('SQL Tutorial is FUN!'); |
| Function |
Description |
Sql |
| CURRENT_TIMESTAMP |
Returns the current date and time |
SELECT CURRENT_TIMESTAMP; |
| DATEADD |
Adds a time/date interval to a date and then returns the date |
SELECT DATEADD(year, 1, '2017/08/25') AS DateAdd; |
| DATEDIFF |
Returns the difference between two dates |
SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff; |
| DATEFROMPARTS |
Returns a date from the specified parts (year, month, and day values) |
SELECT DATEFROMPARTS(2018, 10, 31) AS DateFromParts; |
| DATENAME |
Returns a specified part of a date (as string) |
SELECT DATENAME(year, '2017/08/25') AS DatePartString; |
| DATENAME |
Returns a specified part of a date (as string) |
SELECT DATENAME(year, '2017/08/25') AS DatePartString; |
