Concatenating a variable multiple times in string in MySQL
0
Scenario: I am trying to set a string to a variable and then pass it to a prepared statement. I am using the "concat" function to create the string that will be passed.
Query:
set @floatvar := 'test1'
set @random_var2 := concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount');
Obs: My query has more parts to be included in the string, but even with only this I am already having trouble, so I doing it step by step.
Issue: When I try to run this, I get a syntax error (SQL Error 1064) on line 2. I have no idea why this is happening.
Question: What is causing this issue, and how could it be fixed?
mysql concat heidisql
asked Nov 22 '18 at 10:41
DGMS89DGMS89
5442938
|
show 5 more comments
0
Scenario: I am trying to set a string to a variable and then pass it to a prepared statement. I am using the "concat" function to create the string that will be passed.
Query:
set @floatvar := 'test1'
set @random_var2 := concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount');
Obs: My query has more parts to be included in the string, but even with only this I am already having trouble, so I doing it step by step.
Issue: When I try to run this, I get a syntax error (SQL Error 1064) on line 2. I have no idea why this is happening.
Question: What is causing this issue, and how could it be fixed?
mysql concat heidisql
asked Nov 22 '18 at 10:41
DGMS89DGMS89
5442938
semicolon is missing afterset @floatvar1 := 'test1'
– Madhur Bhaiya
Nov 22 '18 at 10:42
@MadhurBhaiya Just added and it tested again, same issue.
– DGMS89
Nov 22 '18 at 10:43
I can still see syntax errors. It is unclear what you are trying to do here ?
– Madhur Bhaiya
Nov 22 '18 at 10:45
You cannot start a statement with concat.
– P.Salmon
Nov 22 '18 at 10:46
@MadhurBhaiya I am trying to concatenate a string into a variable, but am getting the syntax error.
– DGMS89
Nov 22 '18 at 10:46
|
show 5 more comments
0
0
0
Scenario: I am trying to set a string to a variable and then pass it to a prepared statement. I am using the "concat" function to create the string that will be passed.
Query:
set @floatvar := 'test1'
set @random_var2 := concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount');
Obs: My query has more parts to be included in the string, but even with only this I am already having trouble, so I doing it step by step.
Issue: When I try to run this, I get a syntax error (SQL Error 1064) on line 2. I have no idea why this is happening.
Question: What is causing this issue, and how could it be fixed?
mysql concat heidisql
asked Nov 22 '18 at 10:41
DGMS89DGMS89
5442938
Scenario: I am trying to set a string to a variable and then pass it to a prepared statement. I am using the "concat" function to create the string that will be passed.
Query:
set @floatvar := 'test1'
set @random_var2 := concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount');
Obs: My query has more parts to be included in the string, but even with only this I am already having trouble, so I doing it step by step.
Issue: When I try to run this, I get a syntax error (SQL Error 1064) on line 2. I have no idea why this is happening.
Question: What is causing this issue, and how could it be fixed?
mysql concat heidisql
mysql concat heidisql
asked Nov 22 '18 at 10:41
DGMS89DGMS89
5442938
asked Nov 22 '18 at 10:41
DGMS89DGMS89
5442938
edited Nov 22 '18 at 10:47
DGMS89
asked Nov 22 '18 at 10:41
DGMS89DGMS89
5442938
asked Nov 22 '18 at 10:41
DGMS89DGMS89
5442938
asked Nov 22 '18 at 10:41
DGMS89DGMS89
5442938
5442938
semicolon is missing afterset @floatvar1 := 'test1'
– Madhur Bhaiya
Nov 22 '18 at 10:42
@MadhurBhaiya Just added and it tested again, same issue.
– DGMS89
Nov 22 '18 at 10:43
I can still see syntax errors. It is unclear what you are trying to do here ?
– Madhur Bhaiya
Nov 22 '18 at 10:45
You cannot start a statement with concat.
– P.Salmon
Nov 22 '18 at 10:46
@MadhurBhaiya I am trying to concatenate a string into a variable, but am getting the syntax error.
– DGMS89
Nov 22 '18 at 10:46
|
show 5 more comments
semicolon is missing afterset @floatvar1 := 'test1'
– Madhur Bhaiya
Nov 22 '18 at 10:42
@MadhurBhaiya Just added and it tested again, same issue.
– DGMS89
Nov 22 '18 at 10:43
I can still see syntax errors. It is unclear what you are trying to do here ?
– Madhur Bhaiya
Nov 22 '18 at 10:45
You cannot start a statement with concat.
– P.Salmon
Nov 22 '18 at 10:46
@MadhurBhaiya I am trying to concatenate a string into a variable, but am getting the syntax error.
– DGMS89
Nov 22 '18 at 10:46
semicolon is missing after
set @floatvar1 := 'test1'– Madhur Bhaiya
Nov 22 '18 at 10:42
semicolon is missing after
set @floatvar1 := 'test1'– Madhur Bhaiya
Nov 22 '18 at 10:42
@MadhurBhaiya Just added and it tested again, same issue.
– DGMS89
Nov 22 '18 at 10:43
@MadhurBhaiya Just added and it tested again, same issue.
– DGMS89
Nov 22 '18 at 10:43
I can still see syntax errors. It is unclear what you are trying to do here ?
– Madhur Bhaiya
Nov 22 '18 at 10:45
I can still see syntax errors. It is unclear what you are trying to do here ?
– Madhur Bhaiya
Nov 22 '18 at 10:45
You cannot start a statement with concat.
– P.Salmon
Nov 22 '18 at 10:46
You cannot start a statement with concat.
– P.Salmon
Nov 22 '18 at 10:46
@MadhurBhaiya I am trying to concatenate a string into a variable, but am getting the syntax error.
– DGMS89
Nov 22 '18 at 10:46
@MadhurBhaiya I am trying to concatenate a string into a variable, but am getting the syntax error.
– DGMS89
Nov 22 '18 at 10:46
|
show 5 more comments
2 Answers
2
active
oldest
votes
1
There is no SELECT .. INTO .. like this:
SELECT
concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
INTO @myquery;
Sample
MariaDB [test]> SET @floatvar := 'test1' ;
Query OK, 0 rows affected (0.002 sec)
MariaDB [test]> SELECT
-> concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
-> INTO @myquery;
Query OK, 1 row affected (0.000 sec)
MariaDB [test]>
MariaDB [test]> SELECT @myquery;
+--------------------------------------------------------------------------------+
| @myquery |
+--------------------------------------------------------------------------------+
| SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount |
+--------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [test]>
answered Nov 22 '18 at 10:48
Bernd BuffenBernd Buffen
10.2k21224
Thanks for the answer. Just tried it. If I try to set a value to floatvar ('test' for example) I get the same error as before.
– DGMS89
Nov 22 '18 at 10:56
@DGMS89 - I have add a sample in my answer without any error
– Bernd Buffen
Nov 22 '18 at 11:24
add a comment |
1
set @floatvar := 'test1' ;
set @random_var2 := (select concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount'));
select @random_var2;
gives this
SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount;
Results in
ERROR 1054 (42S22): Unknown column 'test1' in 'field list'
If you do this
prepare sqlstmt = @random_var2;
execute sqlstmt;
deallocte prepare sq;stmt;
result
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= @random_var2' at line 1
Its best to check the prepared statement works before firing into dynamic sql because the error messages from dynamic sql may not be helpful.
answered Nov 22 '18 at 11:00
P.SalmonP.Salmon
8,0152415
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53429115%2fconcatenating-a-variable-multiple-times-in-string-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
There is no SELECT .. INTO .. like this:
SELECT
concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
INTO @myquery;
Sample
MariaDB [test]> SET @floatvar := 'test1' ;
Query OK, 0 rows affected (0.002 sec)
MariaDB [test]> SELECT
-> concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
-> INTO @myquery;
Query OK, 1 row affected (0.000 sec)
MariaDB [test]>
MariaDB [test]> SELECT @myquery;
+--------------------------------------------------------------------------------+
| @myquery |
+--------------------------------------------------------------------------------+
| SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount |
+--------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [test]>
answered Nov 22 '18 at 10:48
Bernd BuffenBernd Buffen
10.2k21224
Thanks for the answer. Just tried it. If I try to set a value to floatvar ('test' for example) I get the same error as before.
– DGMS89
Nov 22 '18 at 10:56
@DGMS89 - I have add a sample in my answer without any error
– Bernd Buffen
Nov 22 '18 at 11:24
add a comment |
1
There is no SELECT .. INTO .. like this:
SELECT
concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
INTO @myquery;
Sample
MariaDB [test]> SET @floatvar := 'test1' ;
Query OK, 0 rows affected (0.002 sec)
MariaDB [test]> SELECT
-> concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
-> INTO @myquery;
Query OK, 1 row affected (0.000 sec)
MariaDB [test]>
MariaDB [test]> SELECT @myquery;
+--------------------------------------------------------------------------------+
| @myquery |
+--------------------------------------------------------------------------------+
| SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount |
+--------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [test]>
answered Nov 22 '18 at 10:48
Bernd BuffenBernd Buffen
10.2k21224
Thanks for the answer. Just tried it. If I try to set a value to floatvar ('test' for example) I get the same error as before.
– DGMS89
Nov 22 '18 at 10:56
@DGMS89 - I have add a sample in my answer without any error
– Bernd Buffen
Nov 22 '18 at 11:24
add a comment |
1
1
1
There is no SELECT .. INTO .. like this:
SELECT
concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
INTO @myquery;
Sample
MariaDB [test]> SET @floatvar := 'test1' ;
Query OK, 0 rows affected (0.002 sec)
MariaDB [test]> SELECT
-> concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
-> INTO @myquery;
Query OK, 1 row affected (0.000 sec)
MariaDB [test]>
MariaDB [test]> SELECT @myquery;
+--------------------------------------------------------------------------------+
| @myquery |
+--------------------------------------------------------------------------------+
| SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount |
+--------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [test]>
answered Nov 22 '18 at 10:48
Bernd BuffenBernd Buffen
10.2k21224
There is no SELECT .. INTO .. like this:
SELECT
concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
INTO @myquery;
Sample
MariaDB [test]> SET @floatvar := 'test1' ;
Query OK, 0 rows affected (0.002 sec)
MariaDB [test]> SELECT
-> concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
-> INTO @myquery;
Query OK, 1 row affected (0.000 sec)
MariaDB [test]>
MariaDB [test]> SELECT @myquery;
+--------------------------------------------------------------------------------+
| @myquery |
+--------------------------------------------------------------------------------+
| SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount |
+--------------------------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [test]>
answered Nov 22 '18 at 10:48
Bernd BuffenBernd Buffen
10.2k21224
edited Nov 22 '18 at 11:23
answered Nov 22 '18 at 10:48
Bernd BuffenBernd Buffen
10.2k21224
answered Nov 22 '18 at 10:48
Bernd BuffenBernd Buffen
10.2k21224
answered Nov 22 '18 at 10:48
Bernd BuffenBernd Buffen
10.2k21224
10.2k21224
Thanks for the answer. Just tried it. If I try to set a value to floatvar ('test' for example) I get the same error as before.
– DGMS89
Nov 22 '18 at 10:56
@DGMS89 - I have add a sample in my answer without any error
– Bernd Buffen
Nov 22 '18 at 11:24
add a comment |
Thanks for the answer. Just tried it. If I try to set a value to floatvar ('test' for example) I get the same error as before.
– DGMS89
Nov 22 '18 at 10:56
@DGMS89 - I have add a sample in my answer without any error
– Bernd Buffen
Nov 22 '18 at 11:24
Thanks for the answer. Just tried it. If I try to set a value to floatvar ('test' for example) I get the same error as before.
– DGMS89
Nov 22 '18 at 10:56
Thanks for the answer. Just tried it. If I try to set a value to floatvar ('test' for example) I get the same error as before.
– DGMS89
Nov 22 '18 at 10:56
@DGMS89 - I have add a sample in my answer without any error
– Bernd Buffen
Nov 22 '18 at 11:24
@DGMS89 - I have add a sample in my answer without any error
– Bernd Buffen
Nov 22 '18 at 11:24
add a comment |
1
set @floatvar := 'test1' ;
set @random_var2 := (select concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount'));
select @random_var2;
gives this
SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount;
Results in
ERROR 1054 (42S22): Unknown column 'test1' in 'field list'
If you do this
prepare sqlstmt = @random_var2;
execute sqlstmt;
deallocte prepare sq;stmt;
result
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= @random_var2' at line 1
Its best to check the prepared statement works before firing into dynamic sql because the error messages from dynamic sql may not be helpful.
answered Nov 22 '18 at 11:00
P.SalmonP.Salmon
8,0152415
add a comment |
1
set @floatvar := 'test1' ;
set @random_var2 := (select concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount'));
select @random_var2;
gives this
SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount;
Results in
ERROR 1054 (42S22): Unknown column 'test1' in 'field list'
If you do this
prepare sqlstmt = @random_var2;
execute sqlstmt;
deallocte prepare sq;stmt;
result
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= @random_var2' at line 1
Its best to check the prepared statement works before firing into dynamic sql because the error messages from dynamic sql may not be helpful.
answered Nov 22 '18 at 11:00
P.SalmonP.Salmon
8,0152415
add a comment |
1
1
1
set @floatvar := 'test1' ;
set @random_var2 := (select concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount'));
select @random_var2;
gives this
SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount;
Results in
ERROR 1054 (42S22): Unknown column 'test1' in 'field list'
If you do this
prepare sqlstmt = @random_var2;
execute sqlstmt;
deallocte prepare sq;stmt;
result
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= @random_var2' at line 1
Its best to check the prepared statement works before firing into dynamic sql because the error messages from dynamic sql may not be helpful.
answered Nov 22 '18 at 11:00
P.SalmonP.Salmon
8,0152415
set @floatvar := 'test1' ;
set @random_var2 := (select concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount'));
select @random_var2;
gives this
SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount;
Results in
ERROR 1054 (42S22): Unknown column 'test1' in 'field list'
If you do this
prepare sqlstmt = @random_var2;
execute sqlstmt;
deallocte prepare sq;stmt;
result
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= @random_var2' at line 1
Its best to check the prepared statement works before firing into dynamic sql because the error messages from dynamic sql may not be helpful.
answered Nov 22 '18 at 11:00
P.SalmonP.Salmon
8,0152415
answered Nov 22 '18 at 11:00
P.SalmonP.Salmon
8,0152415
answered Nov 22 '18 at 11:00
P.SalmonP.Salmon
8,0152415
answered Nov 22 '18 at 11:00
P.SalmonP.Salmon
8,0152415
8,0152415
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53429115%2fconcatenating-a-variable-multiple-times-in-string-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
semicolon is missing after
set @floatvar1 := 'test1'– Madhur Bhaiya
Nov 22 '18 at 10:42
@MadhurBhaiya Just added and it tested again, same issue.
– DGMS89
Nov 22 '18 at 10:43
I can still see syntax errors. It is unclear what you are trying to do here ?
– Madhur Bhaiya
Nov 22 '18 at 10:45
You cannot start a statement with concat.
– P.Salmon
Nov 22 '18 at 10:46
@MadhurBhaiya I am trying to concatenate a string into a variable, but am getting the syntax error.
– DGMS89
Nov 22 '18 at 10:46