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:
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:
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)
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:
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:
If we just restrict themselves by this, we'll get
Converting to a numeric format may look like the following:
Now we should just perform a sorting
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: