LEN function

LEN(string_expression) returns the number of characters in a string. Let’s bound by the case, when the number of characters is greater than one:

SELECT *
FROM (SELECT class +' '+ name AS cn
FROM Ships
UNION ALL
SELECT 'a' AS nc
) x
WHERE LEFT(cn, 1) = RIGHT(cn, 1) AND
LEN(cn) > 1

Realization of this function in MS SQL Server has a feature, namely, LEN() function does not take into account the trailing spaces.

Really, let’s run the following code:

DECLARE @chr AS CHAR(12), @vchr AS VARCHAR(12)
SELECT @chr = 'abcde' + REPLICATE(' ', 5), @vchr = 'abcde'+REPLICATE(' ', 5)
SELECT LEN(@chr), LEN(@vchr)
SELECT DATALENGTH(@chr), DATALENGTH(@vchr)

REPLICATE function adds five blanks to the right from the constant ‘abcde’, which are not taken into account by LEN function, - in both cases we obtain 5.

DATALENGTH function returns number of bytes in a string representation and demonstrates us difference between CHAR and VARCHAR data types. DATALENGTH returns 12 for CHAR data type, and 10 - for VARCHAR.

As one would expect, DATALENGTH for a variable of VARCHAR data type has returned actual length of a variable. But why for a variable of CHAR data type the result became equal 12? The matter is that CHAR is a data type of the fixed length. If value of a variable is less than its length, and we have declared length as CHAR(12), value of a variable will be “leveled” till the declared length by the addition of trailing blanks.

There are tasks on the SQL-EX.RU website that require to normalize (find maximum etc.) in a numerical order a values represented in a string format, for example, a seat number in a plane (“2d”) or a speed of CD (“24x”). The problem lies in a fact that the text sorts like this (ascending)

Indeed,

SELECT '1a' AS place
UNION ALL
SELECT '2a'
UNION ALL
SELECT '11a'
ORDER BY 1

If the sorting of places in order of rows increase is required, the order must be like this:

In order to get such an order, the sorting by a numeric values in text is needed. I offer the following algorith:

1. Extract a number from the string.
2. Convert it to a numeric format.
3. Perform a sorting by a converted value.

As we know that there’s only one letter in the string, and to extract a number we can use the following construction that doesn’t depend on a number of numerals in a seat number:

LEFT(place, LEN(place) - 1)

If we just restrict themselves by this, we’ll get

Converting to a numeric format may look like the following:

CAST (LEFT(place, LEN(place) - 1) AS INT)

Now we should just perform a sorting

SELECT *
FROM (SELECT '1a' AS place
UNION ALL
SELECT '2a'
UNION ALL
SELECT '11a'
) x
ORDER BY CAST(LEFT(place, LEN(place) - 1) AS INT)

As required to proof.

Previously we used the LEFT function to extract a number from a string expression, because we knew a priori how many characters should be removed from the right (one). And what if we should extract a string from a substring not by the known position of a character, but by a character itself? For example, extract all the characters before the first letter “x” (a value of CD speed).

In this case we can also use the previously considered CHARINDEX function that allows us to determine the unknown position of the character:

SELECT model, LEFT(cd, CHARINDEX('x', cd) -1)
FROM PC