In this article, we will learn how to use the String Functions in SQL.
SQL Server has many built-in String Functions.
| Function | Description |
|---|---|
| ASCII() | It returns the ASCII value for the specific character.
SELECT ASCII('CodeHubs') AS FirstCharNumCode;
Output: 67 |
| CHAR() | It returns the character based on the ASCII code.
SELECT CHAR(97) AS CodeToCharacter; Output: a |
| CHARINDEX() | It returns the position of a substring in a string.
SELECT CHARINDEX('hub', 'CodeHubs') AS Position;
Output: 5 |
| CONCAT() | It adds two or more strings together.
SELECT CONCAT('thecodehubs', '.com') AS CONCAT;
--OR
SELECT 'thecodehubs' + '.com' AS CONCAT;
Output: thecodehubs.com |
| CONCAT_WS() | It adds two or more strings together with a separator.
SELECT CONCAT_WS('.', 'www', 'thecodehubs', 'com') AS CONCAT;
Output: www.thecodehubs.com |
| DATALENGTH() | It returns the length of expression in bytes.
SELECT DATALENGTH('.com') as Bytes;
Output: 4 |
| DIFFERENCE() | It compares two SOUNDEX values and returns an integer value.
SELECT DIFFERENCE('thecodehubs', 'codehubs') as SoundexValue;
Output: 2 |
| FORMAT() | It formats a value with the specified format.
SELECT FORMAT(9876543210, '##-##-#####') AS FORMAT; Output: 987-65-43210 |
| LEFT() | It extracts a number of characters from a string (starting from the left).
SELECT LEFT('thecodehubs.com', 3) AS ExtractString;
Output: the |
| LEN() | It returns the length of a string.
SELECT LEN('thecodehubs.com') AS LENGTH;
Output: 15 |
| LOWER() | It converts a string to lower case.
SELECT LOWER('THECODEHUBS') AS LowerCase;
Output: thecodehubs |
| LTRIM() | It removes leading spaces from a string.
SELECT LTRIM(' CodeHubs') AS LeftTrimmedString;
Output: CodeHubs |
| NCHAR() | It returns the Unicode character based on the number code.
SELECT NCHAR(97) AS NumCodeToUnicode; Output: a |
| PATINDEX() | It returns the position of a pattern in a string.
SELECT PATINDEX('%code%', 'thecodehubs') AS Position;
Output: 4 |
| QUOTENAME() | It returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier.
SELECT QUOTENAME('order');
Output: [order] |
| REPLACE() | It replaces all occurrences of a substring within a string, with a new substring.
SELECT REPLACE('thecodehubs Mrticles', 'M', 'A');
Output: thecodehubs Articles |
| REPLICATE() | It repeats a string a specified number of times.
SELECT REPLICATE('thecodehubs ', 3);
Output: thecodehubs thecodehubs thecodehubs |
| REVERSE() | It reverses a string and returns the result.
SELECT REVERSE('thecodehubs') AS ReversedString;
Output: sbuhedoceht |
| RIGHT() | It extracts a number of characters from a string (starting from the right).
SELECT RIGHT('thecodehubs.com', 3) AS ExtractString;
Output: com |
| RTRIM() | It removes trailing spaces from a string.
SELECT RTRIM('CodeHubs ') AS RightTrimmedString;
Output: CodeHubs |
| SPACE() | It returns a string of the specified number of space characters.
SELECT SPACE(5); Output: |
| STR() | It returns a number as a string.
SELECT STR(123); Output: 123 |
| STUFF() | It deletes a part of a string and then inserts another part into the string, starting at a specified position.
SELECT STUFF('thecodehubs.in', 13, 2, 'com');
Output: thecodehubs.com |
| SUBSTRING() | It extracts some characters from a string.
SELECT SUBSTRING('thecodehubs', 4, 4) AS ExtractString;
Output: code |
| TRIM() | It removes leading and trailing spaces, or other specified characters from a string.
SELECT TRIM(' CodeHubs ') AS TrimmedString;
--OR
SELECT TRIM('.! ' FROM ' !CodeHubs. ') AS TrimmedString;
Output: CodeHubs |
| UNICODE() | It returns the Unicode value for the first character of the input expression.
SELECT UNICODE('CodeHubs');
Output: 67 |
| UPPER() | It converts a string to the UPPER CASE.
SELECT UPPER('thecodehubs') AS UPPER;
Output: THECODEHUBS |
Also, check Views In SQL
Bookmarked!, I love your site!
Thank You 🙂