How does select SUBSTRING('uppercase ', LEN('uppercase')-2, 3) result in "a-CodePudding

Please see below query:

select SUBSTRING('uppercase ', LEN('uppercase')-2, 3)

Can someone explain how it is working and giving output as ase

Please help.

CodePudding user response：

SUBSTRING function extracts characters from a string. In additon,LEN function provide the length of string. the LEN('uppercase') would be 9.

SUBSTRING(string, start, length)

your query is like

select SUBSTRING('uppercase ', LEN('uppercase')-2, 3)---equivalent

select SUBSTRING('uppercase ', 9-2, 3) --equivalent
select SUBSTRING('uppercase ', 7, 3)

hence it provide ase

CodePudding user response：

Should be:

select SUBSTRING('uppercase ', LENGTH('uppercase')-2, 3)  // Mysql hasn't len function 

LENGTH('uppercase') // return 9, the length of string.
SUBSTRING('uppercase ', 9-2, 3) // return a substring from the index of 7 and three characters, that is ase

CodePudding user response：

Go through the documentation of substring function

SUBSTRING ( expression ,start , length )

expression
Is a character, binary, text, ntext, or image expression.

start
Is an integer or bigint expression that specifies where the returned characters start. (The numbering is 1 based, meaning that the first character in the expression is 1). If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start length- 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.

length
Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

In your case,

SUBSTRING('uppercase ', LEN('uppercase')-2, 3)
SUBSTRING('uppercase ', 9-2, 3)
SUBSTRING('uppercase ', 7, 3)

u p p e r c a s e
1 2 3 4 5 6 7 8 9
            <====> (From 7 pick 3 characters)

a s e