Question
You have a Microsoft SQL Server 2025 database that contains a table named dbo.CustomerMessages. dbo.CustomerMessages contains two columns named MessageID (int) and MessageRaw (nvarchar(max)).
MessageRaw can contain a phone number in multiple formats, and some rows do NOT contain a phone number.
You need to write a single SELECT query that meets the following requirements:
- The query must return MessageID, RawNumber, DigitsOnly, and PhoneStatus.
- RawNumber must contain the first substring that matches a phone-number pattern, or NULL if no match exists.
- DigitsOnly must remove all non-digit characters from RawNumber, or return NULL.
- PhoneStatus must return valid when a phone number exists in MessageRaw, otherwise return Missing.
How should you complete the Transact-SQL query? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all.
```sql
SELECT
MessageID,
[Target 1] MessageRaw, '\d{3}[)\-\s]*\d{3}[\-\s]*\d{4}' ) AS RawNumber,
[Target 2] MessageRaw, '\d{3}[)\-\s]*\d{3}[\-\s]*\d{4}' ),'\D', '') AS DigitsOnly,
CASE
WHEN [Target 3] MessageRaw, '\d{3}[)\-\s]*\d{3}[\-\s]*\d{4}') = 1
THEN 'Valid'
ELSE 'Missing'
END AS PhoneStatus
FROM dbo.CustomerMessages;
```
NOTE: Each correct selection is worth one point.
Explanation
REGEXP_SUBSTR returns the first substring that matches the pattern, or NULL when nothing matches, which is what RawNumber needs. For DigitsOnly, REGEXP_REPLACE wraps REGEXP_SUBSTR and replaces every non-digit (\D) with an empty string, and a NULL input stays NULL. For PhoneStatus, the expression is compared to 1, so it needs a function that returns a number: REGEXP_COUNT returns an int. REGEXP_LIKE returns a Boolean and is written as a predicate without = 1, and REGEXP_INSTR returns a position, so = 1 would be true only when the number starts the message. STRING_SIMILARITY measures fuzzy similarity rather than pattern matches.
