Set default variables within MySQL functions if values are not correct

Question

when selecting a customers name from a table the database should call a function that formats the name to

lastname, firstname

The function I created is

CREATE DEFINER=`root`@`localhost` FUNCTION `FORMAT_NAME`(firstname VARCHAR(25), lastname VARCHAR(25)) RETURNS varchar(50) CHARSET utf8 RETURN CONCAT(lastname, ', ', firstname)

Within my SELECT I can now write

SELECT FORMAT_NAME(customer.firstname, customer.lastname) as Name

and this returns me

Doe, John

Normally I would solve this by code but I have to use a FUNCTION and have to set up default variables.

How can I define default variables here if a value is empty or null or just not correct?

Daniel E. · Accepted Answer · 2018-02-26 08:11:25Z

You just need to use the COALESCE function :

CREATE DEFINER=`root`@`localhost` FUNCTION `FORMAT_NAME`(firstname VARCHAR(25), lastname VARCHAR(25)) RETURNS varchar(50) CHARSET utf8 RETURN CONCAT(COALESCE(lastname, 'myDefaulValueName'), ', ', COALESCE(firstname, 'myDefaultValueFName'))

Collectives™ on Stack Overflow

Set default variables within MySQL functions if values are not correct

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related