DP-800: Developing AI-Enabled Database Solutions

Question

You have an Azure SQL database containing a table named dbo.Customers. The table contains these columns:

CustomerId (int, primary key)
ProfileJson (nvarchar(max))

An application returns phone numbers in the format +000 000-000-0000, stored in ProfileJson.

Write a query that returns:

One row for each customer
A PhoneNUmerals column containing only digits

Each value may be used once, multiple times, or not at all.

Drag & Drop

WITH PhoneCTE AS
(
    SELECT DISTINCT
        c.CustomerId,
         AS PhoneRaw
    FROM dbo.Customers AS c
)
SELECT
    p.CustomerId,
     AS PhoneNumerals
FROM PhoneCTE AS p
WHERE  IS NOT NULL;

Explanation

JSON_VALUE extracts the scalar phone string at $.contact.phone. REGEXP_REPLACE(p.PhoneRaw, '[^0-9]', '') removes every character other than digits, producing the required digits-only value. Filtering on p.PhoneRaw IS NOT NULL omits customers that have no phone number.

Learn more

DP-800: Developing AI-Enabled Database Solutions

AstroTutor

Notes

Readiness

QuestionQ63

Community Discussion