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
Hello, its good post about media print, we all understand
media is a impressive source of data.
you’re truly a just right webmaster. The web site loading pace is incredible.
It sort of feels that you’re doing any unique trick. In addition, The contents
are masterwork. you’ve done a excellent job in this matter!
Thank You 🙂
Pretty! This has been an extremely wonderful post. Thank you for providing these details.
Thank You 🙂
I was wondering if you ever thought of changing the layout of your blog?
Its very well written; I love what youve got to say. But maybe you could a little more
in the way of content so people could connect with it better.
Youve got an awful lot of text for only having one or two pictures.
Maybe you could space it out better?
Thank You, I will try to do my best.
hi!,I like your writing very much! share we communicate more about your post on AOL? I require a specialist on this area to solve my problem. May be that’s you! Looking forward to see you.