Transact-SQL string and datetime functions

String functions in MS SQL SERVER

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:

Console

Execute

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

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

    Scheme

Realization of this function in MS  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.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,

Console

Execute

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

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

    Scheme

No scheme

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

Console

Execute

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)

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)

    Scheme

No scheme

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:

Console

Execute

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

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

    Scheme

Content:

LEN() function, trailing spaces and uniqueness