Mysql: setting a variable within the select

Question

Given my two db tables aliases and subscriber have entries like this:

aliases.username    = '5551234567'  
aliases.contact     = 'sip:[email protected]'  
subscriber.username = 'a_sip_username'

I'd like to select only the matching rows that have subscriber.username within the aliases.contact field. This was my first attempt but it doesn't return anything:

SELECT aliases.username as phone_number, (@B:=subscriber.username) as user_name FROM aliases,subscriber WHERE aliases.contact regexp "^sip:@B[.*]"

Is this even possible or should I move the logic to the application?

Quassnoi · Accepted Answer · 2009-04-27 15:59:54Z

2

SELECT  aliases.username AS phone_number,
        subscriber.username AS user_name
FROM    aliases, subscriber
WHERE   aliases.contact REGEXP CONCAT('^sip:', subscriber.user_name, '[.*]')

Note that the following query will be more efficient:

SELECT  aliases.username AS phone_number,
        subscriber.username AS user_name
FROM    aliases, subscriber
WHERE   aliases.contact LIKE CONCAT('sip:', subscriber.user_name, '%')

, and this one, though seems complex, is even more efficient:

CREATE FUNCTION fn_get_next_subscriber(initial VARCHAR(200)) RETURNS VARCHAR(200)
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _username VARCHAR(200);
        DECLARE EXIT HANDLER FOR NOT FOUND RETURN UNHEX('FFFF');
        SELECT  username
        INTO    _username
        FROM    subscribers
        WHERE   username>= initial
                AND username NOT LIKE CONCAT(initial, '%')
        ORDER BY
                username
        LIMIT 1;
        RETURN _username;
END

SELECT  a.username AS phone_number,
        s.username AS user_name
FROM    (
        SELECT  si.*, CONCAT('sip:', username) AS fromcontact
        FROM    subscriber si
        ) s, aliases a
WHERE   a.contact >= fromcontact
        AND a.contact < fn_get_next_subscriber(fromcontact)

This will use an index on aliases (contact) and avoid full table scan.

See this article in my blog:

Article-aware title filtering: internationalization: how to JOIN efficiently on a LIKE condition

edited Apr 27, 2009 at 15:59

answered Apr 27, 2009 at 15:49

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

1 Comment

Eddy Over a year ago

Just when I think I've got a handle on sql language I see something that makes me realize I'm speaking in mono-syllable words most of the time. Thanks for the last method Quassnoi; I learned how to fix my problem AND something new, nice!

Collectives™ on Stack Overflow

Mysql: setting a variable within the select

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related