use function for each item in clause mysql

Question

I want to use unhex function for each item in the string seperated by comma. In this string this binary id as string sent by front end.

select * from table where id in
('5534B753765411E6B3FA0021004B111C,1668309A6E6F11E6B405F819928F1AD1,4FE75C954EEB11E6BB970F782D79D6C8')

I want to use this like

select * from table where id in (unhex('5534B753765411E6B3FA0021004B111C'),unhex('1668309A6E6F11E6B405F819928F1AD1'),unhex('4FE75C954EEB11E6BB970F782D79D6C8'))

You can't use unhex in a where clause. Check the mysql docs: dev.mysql.com/doc/refman/5.5/en/… — Marcs
– Marcs, Commented Sep 10, 2016 at 8:10

grepsedawk · Accepted Answer · 2016-09-10 03:42:44Z

1

IN's also support SELECT statements, so you could try using that functionality like so:

SELECT *
FROM table WHERE id IN
(SELECT UNHEX('5534B753765411E6B3FA0021004B111C')
UNION ALL
UNHEX('1668309A6E6F11E6B405F819928F1AD1')
UNION ALL
UNHEX('4FE75C954EEB11E6BB970F782D79D6C8'));

answered Sep 10, 2016 at 3:42

grepsedawk

3,4142 gold badges28 silver badges49 bronze badges

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

3 Comments

user1525591 Over a year ago

code is not working. actually I have to call this inside stored procedure and all above string values will be inside parameter.

e4c5 Over a year ago

It's no fault of pachonk that this example is not working, it's yours for not disclosing full information.

grepsedawk Over a year ago

If this isn't working, please provide the proper information in the question.

Amar Singh · Accepted Answer · 2016-09-10 06:49:00Z

0

select * from table where 
     FIND_IN_SET(id,'5534B753765411E6B3FA0021004B111C,
     1668309A6E6F11E6B405F819928F1AD1,4FE75C954EEB11E6BB970F782D79D6C8');

edited Sep 10, 2016 at 6:49

Amar Singh

5,6223 gold badges30 silver badges56 bronze badges

answered Sep 10, 2016 at 4:17

Alfredo Cortés Flores

12 bronze badges

Collectives™ on Stack Overflow

use function for each item in clause mysql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related