Add some text on result of MySQL Case Operator

mysql case when
mysql case statement multiple conditions
mysql case when in list
mysql case when null
nested case in mysql
mysql update case
mysql order by case
sum(case when mysql)

How I can add some text on result of MySQL Case Operator?

I would like to get some result like this:

<a href="/job/234/php-developer"></a>

I try this but get a error syntax:

SELECT (CASE
  WHEN job_url_outside IS NULL 
    THEN '<a href="/job/', job_id, '/', job_url, '"></a>' 
    ELSE '<a href="', job_url_outside, '" target="_blank" rel="noopener"></a>' 
    END) AS job_url 
  FROM job

Probably you want to concatenate some strings? Then use the following query, where CONCAT is added to do the concatenation:

SELECT (CASE
  WHEN job_url_outside IS NULL 
    THEN CONCAT('<a href="/job/', job_id, '/', job_url, '"></a>') 
    ELSE CONCAT('<a href="', job_url_outside, '" target="_blank" rel="noopener"></a>') 
    END) AS job_url 
  FROM job

MySQL CASE operator, In MySQL, the CASE statement is used to apply a complex Each statement list can contain one or more statements and no empty statement list is allowed. MySQL If used in a string context, the result is returned as a string. MySQL CASE expression is a control flow structure that allows you to add if-else logic to a query. Generally speaking, you can use the CASE expression anywhere that allows a valid expression e.g., SELECT , WHERE and ORDER BY clauses.

You should string concatenate the href string together.

SELECT (CASE WHEN job_url_outside IS NULL THEN '<a href="/job/' + job_id + '/' + job_url + '"></a>' ELSE '<a href="' + job_url_outside + '" target="_blank" rel="noopener"></a>' END) AS job_url FROM job

MySQL CASE Expressions Explained By Practical Examples, In this tutorial, you will learn how to use the MySQL CASE expression to add if-​else If the value does not equal to any value1 , value2 , … For example, if the CASE expression is used in the character string context, it returns the result as a​  The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it will return the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.

Try use:

CONCAT(column,'some text',column)

More information here

In your case it will be like this:

SELECT (CASE
  WHEN job_url_outside IS NULL 
    THEN CONCAT('<a href="/job/', job_id, '/', job_url, '"></a>') 
    ELSE CONCAT('<a href="', job_url_outside, '" target="_blank" rel="noopener"></a>') 
    END) AS job_url 
  FROM job;

DEMO here

MySQL 8.0 Reference Manual :: 12.4 Control Flow Functions, The return type of a CASE expression result is the aggregated type of all result expr2 ) is the more “general” of the two expressions, in the order STRING  You should add some text explaining that the issue is the missing , after oi.orderid, it'll be clearer. – Gorkk Feb 27 '14 at 1:45

12.7 String Functions and Operators, INSERT(), Insert substring at specified position up to specified number of characters If the arguments include any binary strings, the result is a binary string. This function is multibyte safe, and is case-sensitive only if at least one argument  As other answers have pointed out, MySQL also has the IF() function to do this using less verbose syntax. I generally try to avoid this because it is a MySQL-specific extension to SQL that isn't generally supported elsewhere.

MySQL 5.7 Reference Manual :: 12.4 Control Flow Functions, The return type of a CASE expression result is the aggregated type of all result expr2 ) is the more “general” of the two expressions, in the order STRING  MySQL Function & Operators Description; Introduction of MySQL functions and operators: Types of MySQL functions and operators. MySQL Comparison Functions and Operators: Some Comparison Functions and Operators are - Between and; COALESCE() NULL Safe equal to operator (<=>) Equal operator(=) Greater than or equal operator(>=) Greater than operator(>) GREATEST() IN()

MySQL CASE Function, The CASE statement goes through conditions and return a value when the first condition So, once a condition is true, it will stop reading and return the result. The basics for Multiword MySQL Full-Text Search. Let’s talk about the basics for using MySQL Full-Text Search. It can’t be implemented on MySQL default InnoDB storage engine tables. So if you are using InnoDB you need to convert them to MyISAM Engine. Note: From MySQL version 5.6 you can use Full-Text Search with InnoDB storage engine.

Comments
  • Can you share the full error message?
  • Error in query (1064): Syntax error near ' job_id, '/', job_url, '"></a>' ELSE '<a href="', job_url_outside, '" target="_b' at line 1
  • ...probably the MySQL server does not know how to handle all these comma seperated values. Have a look at the documentation about how to use CASE
  • And, for your next question: please always add such an error message to the question, not to the comment section