Mysql: setting a variable within the select

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

aliases.username    = '5551234567'  
aliases.contact     = 'sip:a_sip_username@sip.domain.com'  
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?

From stackoverflow

• 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
  Eddy : Works great, thanks!

  Eddy : 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!