Pull email string from a column, and isolate email domain in another column in a Query

[jensjakobsen]

Hi



I'm building a query - and currently, the source is from one table one. In the query I have a lot of emailaddresses, and I want to add a column where I extract the string AFTER the @-sign, and that string I want to use on my custom column.

In Excel that's easy (sort of), I used formulas (e.g. =RIGHT(B2;LEN(B2)-FIND("@";B2))). Is that possible in a Query? I prefer the query, because, I will need to replace the table multiple times in the near future.

I would appreciate if the solution could be a little detailed, on how to build the query, e.g. do I work within SQL view, or Query builder.

Thanks.

[June7]

SQLView or query builder.

https://support.microsoft.com/en-us/...__toc355966595

An expression would be like:

Mid(fieldname, InStr(fieldname, "@") + 1)

https://support.microsoft.com/en-us/...5-2eb4a099ec72

[jensjakobsen]

Thanks (so far), how would that look "in SQL"? Here's the SQL view in MS Access:

SELECT tblWorkday.[Legal First Name], tblWorkday.[Legal Last Name], tblWorkday.[Email - Work], tblWorkday.[UPN]
FROM tblWorkday;

[jensjakobsen]

Instead of putting it in a query, I tried to add a calculated column at the end of the source table using the somewhat different expression ( I believe I have to use semi-commas, instead of commas, due to regional setting (I live in Denmark)) - here's the expressions:
Mid(UPN, InStr(UPN, "@") + 1) -> Error msg.: THE EXPRESSION YOU HAVE ENTERED HAS AN INVALID . (DOT) OR ! OPERATOR OR INVALID PARENTHESIS (You may have entered an invalid identifier or typed paranthesis following the NULL constant)
Mid(UPN; InStr(UPN; "@") + 1) -> Error msg: SYNTAX ERROR IN EXPRESSION

Any help is appreciated

[June7]

I gave you a link that shows how to build expression in query. What about it did you not understand?

Okay, this is new to me. InStr() function in table calculated field requires all 3 arguments have parameter.

Mid(UPN; InStr(1; UPN; "@") + 1)

[jensjakobsen]

Thanks a lot - apart from the semi-colons (I had to use commas), it works like this:

SELECT (Mid(UPN, InStr(1, UPN, "@") + 1))
FROM tblWorkday;