SQL Character Functions

Summary -

In this topic, we described about the Character Functions with detailed example.

SQL has many built in Functions, Character Functions are one of them. Character Functions accepts character values as input and returns either character or numerical values in result set. The character Functions in SQL are listed as follows –

Function	Description	Syntax
ASCII()	It returns the ASCII value for the required character. In this upper case letters different from lower case letters. If we pass list of characters in the argument list it send ASCII code for the first character and it ignore the rest of the characters.	ASCII('character')
CHAR()	It returns the character value for the specified ASCII value.	CHAR (ASCII value)
CHARINDEX()	It returns the value of position of sub string by searching in the string. If sub string was not found in the string it returns 0. If we don't want to search for the substring in the starting position of string we can mention the searching starting value in the function by start value. Start value is optional. case-insensitive search is performed by this function while checking substring in a string. By default First position of string is 1.	CHARINDEX('sub string', 'string', start value)
CONCAT()	This function add the two or more strings together to form one string.	CONCAT('string1', 'string2', 'string3',..)
Concat with +	By using + operator we can add two or more strings together.	'string1'+ 'string2' + 'string3',..
CONCAT_WS()	It adds two or more strings together with a separator.	CONCAT_WS (separator, 'string1', 'string2', 'string3',..)
DATALENGTH()	It returns count of the length of the expression. It count the starting and middle and after spaces also in expression while counting length of the expression. If the expression is NULL, It returns NULL value.	DATALENGTH('expression')
SOUNDEX()	It returns the Four-character code in result set. It is used to check the similarity between two expressions.	SOUNDEX('expression')
DIFFERENCE()	It returns an integer value by comparing two Soundex values. The integer value vary from 0 to 4. 0 indicates weak matching, 4 indicates strong matching of two Soundex values.	DIFFERENCE ('expression1', 'expression2')
FORMAT()	It Formats date or time or numeric values into required format.	FORMAT(value, Format, culture)
LEFT()	It returns the specified extracted characters in result set. It extracts from from left of the string.	LEFT('expression', no_of_chars)
RIGHT()	It returns the specified extracted characters in result set. It extracts from right of the string.	RIGHT('expression', no_of_chars)
LEN()	It returns the length of the specified string. It count the starting and middle and spaces also in expression while counting length of the expression. If the expression is NULL, It returns NULL value.	LEN('expression')
LOWER()	It returns specified expression in lower case in result set.	LOWER('expression')
UPPER()	It returns the specified expression in upper case in result set.	UPEER('expression')
LTRIM()	It trims the starting spaces on string and returned it in result set.	LTRIM('expression')
RTRIM()	It trims the ending spaces on string and returned it in result set.	RTRIM('expression')
NCHAR()	It returns Unicode character for the specified number code value.	NCHAR(number_code)
PATINDEX()	It returns the position of pattern in the string. If sub string was not found in the string it returns 0. This function search pattern by case- insensitive. The pattern should between the % symbol. The various wildcards are used in the pattern are listed as below – % - any pattern of any length. _ - match single character. [ ] – matched any one characters in the brackets. [ ^]- match except those characters with in the brackets.	PATINDEX('PATTERN', string)
QUOTENAME()	It returns specified Unicode character data with delimiters. Unicode characters limited to 128 characters only. quote_char that are used are as left or right parenthesis ( ), left or right brackets [ ], left or right < >, left or right { }. If we don't mention the quote_char or if specified single quotes, by default brackets are used.	QUOTENAME (string, quote_char)
REPLACE()	It replaces the substring in a string with another new sub string.	REPLACE(String, old_sub_string, new_sub_string)
REPLICATE()	It repeats the string in result set with specified number of times.	REPLICATE('string', integer)
REVERSE()	It returns the reversed specified string in result set.	REVERSE('string')
SPACE()	It returns the string in result set with specified number of spaces	SPACE(number)
STUFF()	It delete the specified characters in the string and insert another new characters in those paces.	STUFF('string', )
STR()	It returns the numerical value into string format. We can specify how many digits we want display in result set. We can specify how many decimals we want in result set, but it is optional.	STR(number, no_of_digits, no_of_decimals)
SUBSTRING()	It returns the specified extract characters in result set from the string. We can specify from which position of string we want to extract and how many characters we want extract from string.	SBSTRING('string', position, no_of_charactrers)
TRIM()	It delete the starting spaces present in the string or specified characters in the string.	TRIM([Characters from ] 'string')
TRANSLATE()	It returns first argument string value in result set when the second argument string characters replaced with third argument string characters.	TRANSLATE('string1', 'string2', 'string3')
UNICODE()	It returns an integer value i.e. Unicode value for the specified first character in the expression.	UNICODE('expression')

The character Functions that are used in SQL are explained with examples are as follows -

Function

Example

Output

ASCII()

SELECT ASCII('Employee');

Now we can get ASCII value for E and rest of the characters ignored. The output was as follows – A69

CHAR()

SEELCT CHAR(69) AS A, CHAR(99) AS B;

Now we can get corresponding character for the specified ASCII value was as follows –

A	B
E	c

CHARINDEX()

SELECT CHARINDEX('t', 'DEPARTMENT',7 ) AS A, CHARINDEX('t', 'DEPARTMENT') AS B, CHARINDEX('O', 'DEPARTMENT') AS C;

Now we can get output like as shown in below – For the first query it checks 't' from the 7th position in 'DEPARTMENT' string, Next it checks from starting position.

A	B	c
10	6	0

CONCAT()

SELECT CONCAT('EMP','LOYEE')AS A;

Now we can get output like as shown in below –

A
EMPLOYEE

Concat with +

SELECT 'emp' + 'loyee' AS A;

Now we can get output like as shown in below -

A
employee

CONCAT_WS()

SELECT CONCAT_WS('_','employee','details')AS A;

Now we can get output like as shown in below -

A
employee_details

DATALENGTH()

SELECT DATALENGTH(' empl oyee ') AS A;

Now we can get output like as shown in below –

A
11

SOUNDEX()

SELECT SOUNDEX('employee') AS A;

Now we can get output like as shown in below -

A
E514

DIFFERENCE()

SELECT DIFFERENCE('EMP', 'LOYEE') AS A;

Now we can get output like as shown in below –

A
1

FORMAT()

SELECT FORMAT(123456789, '@#@#-##-##$###') AS A, FORMAT(GETDATE(), '', 'zu') AS 'Zulu Result';

Now we can get output like as shown in below –

A	ZULU Result
@1@2-3_4-56$789	9/14/2020 12:01:33

LEFT()

SELECT LEFT('employee', 3) AS A, LEFT('employee',9) AS B;

Now we can get output like as shown in below –

A	B
emp	employee

RGHT()

SELECT RIGHT('employee', 3) AS A;

Now we can get output like as shown in below -

A
yee

LEN()

SELECT LEN(' emp loyee ') AS A, LEN(' emp loyee') AS B;

Now we can get output like as shown in below –

A	B
10	10

LOWER()

SELECT LOWER('EMPLOYEE') AS A;

Now we can get output like as shown in below –

A
employee

UPPER()

SELECT UPPER('employee') AS A;

Now we can get output like as shown in below –

A
EMPLOYEE

LTRIM()

SELECT LTRIM(' employee') AS A;

It trims the starting spaces i.e. on left side of string. Now we can get output like as shown in below –

A
employee

RTRIM()

SELECT RTRIM('employee') AS A;

It trims the ending spaces i.e. on right side of string. Now we can get output like as shown in below –

A
employee

NCHAR()

SELECT NCHAR(100) AS A;

Now we can get output like as shown in below -

A
d

PATINDEX()

SELECT PATINDEX('%_L%', 'employee') AS A, PATINDEX('%[l]%', 'employee') AS B, PATINDEX('%[^Y]%', 'employee') AS C;

Now we can get output like as shown in below -

A	B	C
3	4	1

QUOTENAME()

SELECT QUOTENAME('employee') AS A, QUOTENAME('employee', '') AS B, QUOTENAME('employee', '{') AS C;

Now we can get output like as shown in below -

A	B	C
[employee]	[employee]	{employee}

REPLACE()

SELECT REPLACE('EMPLOYEE', 'PLO', 'LOP');

Now we can get output like as shown in below –

A
employee

REPLICATE()

SELECT REPLICATE ('employee ' , 2) AS A;

Now we can get output like as shown in below –

A
employee employee

REVERSE()

SELECT REVERSE('employee') AS A;

Now we can get output like as shown in below -

A
eeyolpme

STUFF()

SELECT STUFF('employee',2, 3,'FLY');

Now we can get output like as shown in below –

A
eFLYoyee

STR()

SELECT STR(9845.258569,8) AS A, STR(9845.258569,8, 4) AS B;

Now we can get output like as shown in below –

A	B
9845	9845.259

SUBSTRING()

SELECT SUBSTRING('employee',2, 3) AS A;

Now we can get output like as shown in below –

A
mpl

TRIM()

SELECT TRIM('employee')AS A, TRIM('e!'FROM'employee')AS B;

Now we can get output like as shown in below –

A	B
employee	mploy

TRANSLATE()

SELECT TRANSLATE('EMPLOYEE','EMP','emp') AS A;

Now we can get output like as shown in below –

A
empLOYee

UNICODE()

SELECT UNICODE('employee') AS A;

Now we can get output like as shown in below –

A
101