Question
You have a SQL database in Microsoft Fabric that contains a column named Payload. Payload stores customer data in JSON documents that have the following format.
```json
{
"date": "2020-01-25",
"customer_email": "user@contoso.com",
...
}
```
Data analysis shows that some customers have subaddressing in their email address, for example, user1+tag@contoso.com.
You need to return a normalized email value that removes the subaddressing, for example, user1+tag@contoso.com must be normalized to user1@contoso.com.
Which Transact-SQL expression should you use?
- A. REGEXP_REPLACE(JSON_VALUE(Payload, '$.customer_email'), '\+.*$', '')
- B. REGEXP_SUBSTR(JSON_VALUE(Payload, '$.customer_email'), '^[^+]+@.*$=')
- C. REGEXP_REPLACE(JSON_VALUE(Payload, '$.customer_email'), '\+.*@', '@') Correct
- D. REGEXP_REPLACE(JSON_VALUE(Payload, '$.customer_email'), '\+.*', '')
Explanation
The pattern \+.*@ matches from the plus sign through the at sign, and replacing that match with @ removes only the tag, so user1+tag@contoso.com becomes user1@contoso.com. Addresses with no plus sign do not match, and REGEXP_REPLACE returns them unchanged. Options A and D match from the plus sign to the end of the string and replace it with nothing, which also removes the domain and leaves user1. Option B cannot return the normalized value: REGEXP_SUBSTR only extracts text, and its pattern ends with a stray equals sign and does not allow a plus sign before the at sign.
