Exam DP-800 Topic 1 Question 42 Discussion
Actual exam question for Microsoft's DP-800 exam
Question #: 42
Topic #: 1
Question #: 42
Topic #: 1
You have a SQL database in Microsoft Fabric that contains a column named Payload. pay load stores customer data in JSON documents that have the following format.
Data analysis shows that some customers have subaddressing in their email address, for example, [email protected].
You need to return a normalized email value that removes the subaddressing, for example, user! + [email protected] must be normalized to [email protected].
Which Transact SQL expression should you use?
Data analysis shows that some customers have subaddressing in their email address, for example, [email protected].
You need to return a normalized email value that removes the subaddressing, for example, user! + [email protected] must be normalized to [email protected].
Which Transact SQL expression should you use?
Suggested Answer: C Vote an answer
The correct answer is C because the email must be normalized by removing only the subaddressing portion between the plus sign and the @, while preserving the domain. JSON_VALUE is the correct function to extract the scalar email value from the JSON document. Microsoft states that JSON_VALUE is used to extract a scalar value from JSON text.
Then REGEXP_REPLACE should remove the pattern \+.*@ and replace it with a single @. For example:
* [email protected] # [email protected]
Microsoft documents that REGEXP_REPLACE returns the source string with text matching the regular expression replaced by the replacement string, and that an empty or custom replacement string can be used to reshape the result.
Why the other options are wrong:
* A removes everything from + to the end of the string, which would leave user1 and lose @contoso.com.
* B tries to extract a string that already excludes +..., but it does not reliably reconstruct the normalized address in this pattern.
* D also removes everything after +, including the domain, which is incorrect.
So the normalized-email expression is:
REGEXP_REPLACE(JSON_VALUE(Payload, ' $.customer_email ' ), ' \+.*@ ' , ' @ ' )
Then REGEXP_REPLACE should remove the pattern \+.*@ and replace it with a single @. For example:
* [email protected] # [email protected]
Microsoft documents that REGEXP_REPLACE returns the source string with text matching the regular expression replaced by the replacement string, and that an empty or custom replacement string can be used to reshape the result.
Why the other options are wrong:
* A removes everything from + to the end of the string, which would leave user1 and lose @contoso.com.
* B tries to extract a string that already excludes +..., but it does not reliably reconstruct the normalized address in this pattern.
* D also removes everything after +, including the domain, which is incorrect.
So the normalized-email expression is:
REGEXP_REPLACE(JSON_VALUE(Payload, ' $.customer_email ' ), ' \+.*@ ' , ' @ ' )
by Vincent at Sep 10, 2026, 04:43 AM
0
0
0
10
Comments
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Report Comment
Commenting
You can sign-up / login (it's free).