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

CodePudding user response：

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   |

CodePudding user response：

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

fiddle