How To Use String Functions In SQL

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

20 Comments

  1. Earnest Failing

    Bookmarked!, I love your site!

    0
    0
    Reply
    1. Thank You 🙂

      0
      0
      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories