MySQL and Regex on INT

Question

This query should return 4 rows but returns 1 with the ID of 62983. Any reason why?

SELECT *
    FROM sims_user_role_maps surm
    JOIN sims_role sr ON surm.role_id = sr.id
    WHERE 
        surm.user_id = 118730
        AND sr.organization_id REGEXP 62978|62981|62982|62983

SP Call

call schools_remove_for_user('f155ec0e-b9ce-11e1-a1fa-001cc4565d26', '62966|62969|62983')

SP

CREATE PROCEDURE `schools_remove_for_user`(
    user_id NVARCHAR(255),
    school_ids LONGTEXT
)
BEGIN

DECLARE sims_user_id INT DEFAULT NULL;

SELECT u.sims_id 
INTO sims_user_id 
FROM user u 
WHERE u.id = user_id;

SET SQL_SAFE_UPDATES = 0;

DELETE FROM sims_user_role_maps
WHERE 
        user_id = sims_user_id
    AND role_id IN (
                SELECT role_id 
                FROM(
                    SELECT surm.role_id 
                    FROM sims_user_role_maps surm
                    JOIN sims_role sr ON surm.role_id = sr.id
                    WHERE 
                            surm.user_id = sims_user_id
                        AND sr.organization_id REGEXP school_ids) 
                A);

END

are you sure those other rows exist? 78, 81, 82? And have a user_id = 118730 in the surm table? — MJB
– MJB, Commented Jul 16, 2012 at 18:53
This is a dynamic string, and yes they do belong to that user ID. I even removed the last ID 62983 and it is still bringing back that only row. — Mike Flynn
– Mike Flynn, Commented Jul 16, 2012 at 18:55

Jon · Accepted Answer · 2012-07-16 18:54:33Z

1

You did not place the regular expression inside quotes, so | works as bitwise OR.

The correct syntax is AND sr.organization_id REGEXP '62978|62981|62982|62983'. You can also consider sr.organization_id IN (x, y, z) which is better if you are just trying to match specific integers.

answered Jul 16, 2012 at 18:54

Jon

439k85 gold badges756 silver badges820 bronze badges

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

2 Comments

Mike Flynn Over a year ago

This works directly when I execute the SQL but in a stored proc it doesnt.

Jon Over a year ago

@MikeFlynn: Are you sure that the SP doesn't have some other problem?

Mike Flynn · Accepted Answer · 2012-07-16 20:01:03Z

0

The reason was the user_id in the delete statement. I added the table name as the prefix.

DELETE FROM sims_user_role_maps
WHERE 
        sims_user_role_maps.user_id = sims_user_id
    AND sims_user_role_maps.role_id IN (

answered Jul 16, 2012 at 20:01

Mike Flynn

23.4k59 gold badges199 silver badges373 bronze badges

Collectives™ on Stack Overflow

MySQL and Regex on INT

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related