mysql how to concat/append binary data type

Question

I am trying to write a stored function which will return BINARY(20) type. I would like to format this return value, by putting string, itn, float values in it. But I couldn't figure it out how can I append binary data.

CREATE FUNCTION `test`() RETURNS binary(20)
BEGIN
declare v binary(20);

set v:= CAST('test' as binary);
set v := v || cast(5 as binary); -- I would like to append 5 as binary but how?

return v;
END

First line writes test as binary, at second line I would like to append 5 as binary. How can I do that? Thank you all..

Ike Walker · Accepted Answer · 2011-03-16 13:15:22Z

2

|| in mysql is logical OR - you want to use CONCAT:

SET v := CONCAT(v, CAST(5 AS BINARY));

instead of CAST(5 AS BINARY), you can use the shorthand BINARY 5.

edited Mar 16, 2011 at 13:15

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

answered Mar 16, 2011 at 12:50

ax.

60.4k9 gold badges85 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MCA Over a year ago

I tried that but it converts 5 to char, I also tried number 659845 instead of 5, it uses 6bytes instead of 4 bytes, so it is not binary.

ax. Over a year ago

what do you want the 5 to be converted to? BINARY means "byte string", so naturally an integer 5 is converted to the byte string '5'.

ax. Over a year ago

so did you make any progress in solving your problem? did you maybe figure out that packing different types into a BINARY string is not the best of ideas?

Sara Over a year ago

I confirmed this answer. Here I convert two strings to binary data, concatenate them, and then convert the result back to a string:

select concat(binary 'x', binary 'y') as binary_return_value, cast(concat(binary 'x', binary 'y') as char) char_return_value;

tanderson · Accepted Answer · 2015-07-15 01:56:53Z

0

To concat 5, rather than character 5, use char(5) rather than binary 5. So:

select concat("test", char(5))

returns a blob of 5 bytes. You can verify this with:

select length(concat("test", char(5))), hex(concat("test", char(5)));

To pad it out to a 20 byte array:

select convert(concat("test", char(5)), binary(20));

In your stored procedure, you just need:

set v:= concat("test", char(5));

answered Jul 15, 2015 at 1:56

tanderson

1,22916 silver badges33 bronze badges

Collectives™ on Stack Overflow

mysql how to concat/append binary data type

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related