I am trying to extract the left part of an email field based on the separator @.
For instance, from the following email: root@localhost I want to extract root.
I wrote:
select email, substr(email,1,strpos(email, '@')-1)
from user;
but I get:
[22011] ERROR: negative substring length not allowed
How can I make the substring inside a function?
Your code works well for valid email addresses. But if you don't have any @ chars in your strings it fails.
So you might have invalid email addresses in your table.
I usually solve this just by adding a @ to the end:
select email, substr(email, 1, strpos(email || '@', '@') - 1)
from user;
Or one of these alternatives:
select email, left(email, strpos(email || '@', '@') - 1)
select email, substring(email from '^[^@]*')
when you use strpos function but didn't find the character in the string.
I would use POSITION function to get charter position and do substring to get the value, because if it didn't find character will return 0 instead of an error.
CREATE TABLE T(
email varchar(50)
);
insert into t values ('[email protected]');
insert into t values ('11.com');
insert into t values ('[email protected]');
Query 1:
select CASE WHEN POSITION('@' in email) > 0 THEN
substr(email,1,POSITION('@' in email)-1)
ELSE ''
END
FROM T
| case |
|------|
| aa |
| |
| test |
