SQL Server String Functions-5

A string function accepts a string value as an input and returns a string value regardless of the data type. They’re used to convert an existing string value to a different format, change the letter cases, retrieve a portion of it, or determine the length of it. They are utilized in reports, logic implementation, and data analysis on a daily basis.

This will be a series of postings since we will go over each one in detail with one or more examples for simple comprehension. So continue reading.

For the previous article, click here. In this article, we’ll be discussing the following and the remaining built-in String functions.

SOUNDEX
SPACE
STR

SOUNDEX:
It returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. As per the documentation, SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken in English. The first character of the code is the first character of character_expression, converted to upper case. The second through fourth characters of the code are numbers that represent the letters in the expression. The letters A, E, I, O, U, H, W, and Y are ignored unless they are the first letter of the string. Zeroes are added at the end if necessary to produce a four-character code.

Examples:
SELECT SOUNDEX(‘Own’), SOUNDEX(‘Won’);
Returns: O500-W500
SELECT SOUNDEX(‘Night’), SOUNDEX(‘Knight’);
Returns: N230-K523
SELECT SOUNDEX(‘Right’), SOUNDEX(‘Write’);
Returns: R230-W630
SELECT SOUNDEX(‘Juicy’), SOUNDEX(‘Juice’);
Returns: J200-J200
SELECT SOUNDEX(‘leave’), SOUNDEX(‘live’);
Returns: L100-L100
SELECT SOUNDEX(‘John’), SOUNDEX(‘John’);
Returns: J500-J500

SPACE:
It returns a string of repeated spaces.

Example:
SELECT RTRIM(LastName) + ‘,’ + SPACE(2) + LTRIM(FirstName) FROM Employee;
Returns: John Smith

STR:
Returns character data converted from numeric data. The character data is right-justified, with a specified length and decimal precision.

Example:
SELECT STR(123.45, 4, 2);
Returns: 123
SELECT STR(123.45, 5, 2);
Returns: 123.5
SELECT STR(123.45, 6, 2);
Returns: 123.45