DP-800: Developing AI-Enabled Database Solutions

You have a Microsoft SQL Server 2025 database containing a table named dbo.CustomerMessages. dbo.CustomerMessages has two columns: MessageID (int) and MessageRaw (nvarchar(max)).

MessageRaw can include a phone number in several formats, and some rows do not include a phone number.

Write one SELECT query that meets these requirements:

• Return MessageID, RawNumber, DigitsOnly, and PhoneStatus.
• RawNumber must contain the first substring that matches a phone-number pattern, or NULL when no match is found.
• DigitsOnly must remove every non-digit character from RawNumber, or return NULL.
• PhoneStatus must return Valid when MessageRaw contains a phone number; otherwise, it must return Missing.

Each value may be used once, more than once, or not at all.

SELECT
    MessageID,
    MessageRaw, '\d{3}[\.\-\s]\d{3}[\.\-\s]\d{4}') AS RawNumber,
    MessageRaw, '\d{3}[\.\-\s]\d{3}[\.\-\s]\d{4}'), '\D', '') AS DigitsOnly,
    CASE
        WHEN MessageRaw, '\d{3}[\.\-\s]\d{3}[\.\-\s]\d{4}') = 1
        THEN 'Valid'
        ELSE 'Missing'
    END AS PhoneStatus
FROM dbo.CustomerMessages;