Is there any way to assign a name to a function in SQL which can be referenced later?-CodePudding

While practicing on sakila database in SQL, below is the code that I have written to concatenate the first name and last name of actors. Now I would like to assign a name to the whole concat function which I can use to reference the concat function in the length function below it.

Please help with this query.

SELECT
CONCAT(CONCAT(LEFT(first_name, 1),LOWER(SUBSTRING(first_name, 2))," ",CONCAT(LEFT(last_name, 1), LOWER(SUBSTRING(last_name, 2))))) Actor_Name,

LENGTH(CONCAT(CONCAT(LEFT(first_name, 1),LOWER(SUBSTRING(first_name, 2))," ",CONCAT(LEFT(last_name, 1), LOWER(SUBSTRING(last_name, 2)))))) Name_Length
FROM actor;

CodePudding user response：

Use a derived table (i.e. the subquery):

SELECT Actor_Name, length(Actor_Name) Name_Length
FROM
(
    SELECT
        CONCAT(CONCAT(LEFT(first_name, 1),LOWER(SUBSTRING(first_name, 2))," ",CONCAT(LEFT(last_name, 1), LOWER(SUBSTRING(last_name, 2))))) Actor_Name
    FROM actor
)

CodePudding user response：

On MySQL 5.x you may use user-defined variable:

SELECT @actor_name := CONCAT(CONCAT(LEFT(first_name, 1),LOWER(SUBSTRING(first_name, 2))," ",CONCAT(LEFT(last_name, 1), LOWER(SUBSTRING(last_name, 2))))) Actor_Name,
       LENGTH(@actor_name) Name_Length
FROM actor;

This technique may work in current MySQL versions too, but it is defined as deprecated one and it will be removed in some next version.

PS. You do not need in UPPER/LOWER functions while calculating the length. I don't know the symbol which changes its length during case change... LENGTH(first_name) LENGTH(last_name) 1 AS Name_Length is enough.