MySql Error: Truncated incorrect DOUBLE value-CodePudding

I have seen a lot of Q&A about this, but none address my scenario.

To simplify things, I am providing simplified code from a script rather than the entire function. The code below reproduces the problem:

SET @Loc = 'xxx';
SET @RegionName = '';
SET @RegionName = @Loc   ' - '    @Loc;
SELECT @RegionName;

this was the output:

and 3 warnings:

Truncated incorrect DOUBLE value: 'xxx'
Truncated incorrect DOUBLE value: ' - '
Truncated incorrect DOUBLE value: 'xxx'

I'm a SQL Server developer trying to migrate to MySql and appreciate any help you can offer.

Thank you.

CodePudding user response：

The error seems to be on this line where I concatenate two strings:

SET RegionName = AccountName ' - ' Region;

Whoah there. The operator is string concatenation in Microsoft SQL Server and Microsoft Access, but not in MySQL (or other products that implement SQL).

To do string concatenation in MySQL, use the CONCAT() function:

SET RegionName = CONCAT(AccountName, ' - ',  Region);

If you use , then you're doing arithmetic addition, not string concatenation. How does arithmetic addition work when the arguments are strings? First arguments are implicitly converting from strings to their numeric value based on the leading digits, ignoring any characters after the first non-digit.

Once you understand how string concatenation should be done in MySQL, I bet a lot of your issues will go away.

For the record, standard ANSI SQL uses || as the string concatenation operator. This is not the default semantics in MySQL, but you can enable it with the SQL mode PIPES_AS_CONCAT.