In this article, we will learn how to use the String Functions in SQL.

SQL Server has many built-in String Functions.

FunctionDescription
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

Subscribe

Select Categories