Question 22054 in Mysql Question posted in
The official MySQL documentation can be found here.

Find the substring between the first 'a' char in email column before '@' – Mysql

I am trying to find the substring between the first ‘a’ char in email column before ‘@’,

i have written the below sql-query ,

but i think i can do it in better way .

SELECT 
    email,
    CASE
        WHEN
            LENGTH(SUBSTR(email, 0, POSITION('@' IN email))) - LENGTH(REPLACE(SUBSTR(email, 0, POSITION('@' IN email)),
                        'a',
                        '')) > 1
        THEN
            SUBSTR(email,
                POSITION('a' IN email) + 1,
                POSITION('a' IN SUBSTR(email,
                        POSITION('a' IN email) + 1)) - 1)
        ELSE ''
    END AS deelstring
FROM
    persoon

correction for the sql-query

Tags:

2

Answers


  1. 0

    You have to test every step of your query epecially, when you want to extract parts.

    As ysth Length only can count ascii charakter, which doesn’t matter when you check for if there are any a in the email, but when extracting you need the length for all characters even non ASCII.

    CREATE TABLe persoon (email varchar(50))

    INSERT INTO persoon VALUEs('[email protected]'),('tÚsta11Ú[email protected]')

    Records: 2  Duplicates: 0  Warnings: 0

    SELECT 
    email,
      CASE
        WHEN
            LENGTH(SUBSTR(email, 1, POSITION('@' IN email)-1)) - LENGTH(REPLACE(SUBSTR(email, 1, POSITION('@' IN email)-1),
                        'a',
                        '')) >= 1
        THEN
             SUBSTR(email,
                POSITION('a' IN email) + 1,
                CHAR_LENGTH (SUBSTR(email, 1, POSITION('@' IN email)-1))
  - POSITION('a' IN email) )
  ELSE ''
    END AS deelstring
FROM
    persoon
    email deelstring
    [email protected] 11111111
    tÚsta11Ú[email protected] 11Ú111111

    fiddle

    Login or Signup to reply.
  2. 0

    There’re at least two ways to extract a substring:

    1. Extract the substring from start position to end position, or
    2. Remove prefix/suffix before/after start/end position

    Piggyback on data prepared by @nbk with the 2nd approach, this may work for you:

    select email,
       regexp_replace(email,'^.*a','') as remove_prefix,
       regexp_replace(email,'@.*$','') as remove_suffix,
       regexp_replace(regexp_replace(email,'^.*a',''),'@.*$','') as remove_both
  from persoon;

    Outcome:

    email                 |remove_prefix    |remove_suffix|remove_both|
----------------------+-----------------+-------------+-----------+
[email protected]|[email protected]|testa11111111|11111111   |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search