I'm trying to get the primary email from aliases, for example, I have the following examples and I need to normalize them.

1. from "[email protected]" to "[email protected]"
2. from "test [email protected]" to "[email protected]"
3. from "test.me.123 [email protected]" to "[email protected]"
4. from "test [email protected]" to "[email protected]"

I wrote a SQL statement on bigquery but got an error that says "Third argument in SUBSTR() cannot be negative"

SELECT  TRANSLATE(SUBSTR('test [email protected]', 1, INSTR('test [email protected]', ' ')-1),' .', '') ||'@' || SUBSTR('test [email protected]', Instr('test [email protected]','@') 1)

Any ideas or suggestions for the above or ideas for useing regex_replace instead

Thanks

CodePudding user response：

You seem to try to replace and . characters by noting (``) using:

TRANSLATE(SUBSTR('test [email protected]', 1, INSTR('test [email protected]', ' ')-1),' .', '')

This is not working because the second and third parameter using TRANSLATE() should have equal length.

Maybe you want to do (for the part before the @):

REPLACE(REPLACE(LEFT('test [email protected]',INSTR('test [email protected]','@')-1),' ',''),'.','')

CodePudding user response：

Maybe you could try regex but I am not sure whether the performance is good or bad.

select CONCAT(REGEXP_EXTRACT("test.me [email protected]", r'([\w\.] )'), REGEXP_EXTRACT("test.me [email protected]", r'(@[\w\.] )'))