Not escaping a mysql variable within a string.

Posted on 2005-04-12
Medium Priority
Last Modified: 2006-11-18
I have the following query:

SELECT @test := 'hello', CONCAT('Message: @test', column_name)

The problem is that this displays "Message: @test", instead I want to display the variable @test that I set right before it, although, I do NOT want to put it seperately like this:

CONCAT('Message:', @test, column_name)

Is there anyway to evaluate a variable enrapped inside quotes without seperating it from the rest of the string so that it shows its value?
Question by:andw928
  • 2
  • 2
LVL 33

Expert Comment

ID: 13767832
No.  Why don't you want to list it separately?
LVL 10

Expert Comment

ID: 13768188
It is not doing what it should because how it can distinguish whether you want a variable substitution or the string in itself (i.e. @test in this case). By putting it inside the quotes ' ', you are making it a string.

Author Comment

ID: 13768391
snoyes, because I am passing a coldfusion function which can't be seperated.
kupra1, you did not read my entire post.

Does anybody else have a possible solution?
LVL 33

Accepted Solution

snoyes_jw earned 400 total points
ID: 13771340
AFAIK, MySQL does not support any kind of automatic variable replacement inside string literals.

You could do this:
REPLACE(CONCAT('Message: @test', column_name), '@test', @test)

Author Comment

ID: 13776660
You are a genius my friend, it works well, thank you!

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question