SQL Function

By ukmodak | June 6th 2022 12:23:03 PM | viewed 219 times
SQL Server String Functions
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!');
SQL Server Math/Numeric Functions
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;
bONEandALL
Visitor

Total : 18980

Today :9

Today Visit Country :

  • Germany
  • Singapore
  • United States
  • Russia