convert comma separated string values into integer values in mysql

Question

I have a comma separated string variable v_combined_prodid . But i wants to convert comma separated string variable to comma separated int value or in loop where we can update one table with where clause prodid as int. we cant use FIND_IN_SET(prodid, v_combined_prodid )

we can use stored procedure.

DECLARE v_combined_prodid varchar(800);

set v_combined_prodid ='1,2,3,4,5';

below statement exactly requirement.

update mytable t set t.status=2
 WHERE prodid in (1,2,3,4,5)
 and t.status=0 ;

P Nayak, did the answer below help you? If so, you have the option of marking the answer as accepted. It's your choice. Marking your question with an accepted answer will give closure to your question. — zedfoxus
– zedfoxus, Commented Aug 11, 2021 at 18:24

zedfoxus · Accepted Answer · 2021-03-04 03:29:13Z

0

You are better off creating a dynamic query and then execute it.

See Mysql dynamically build query string in a stored procedure based on logic for an example.

DECLARE v_combined_prodid varchar(800);

set v_combined_prodid ='1,2,3,4,5';

set @query = CONCAT(' update mytable t set t.status = 2 where prodid in ('
                    , v_combined_prodid
                    , ') and t.status = 0');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered Mar 4, 2021 at 3:29

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

convert comma separated string values into integer values in mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related