PhoneRaw extraction → JSON_VALUE(c.ProfileJson, ' $.contact.phone ' )

PhoneNumerals → REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' )

WHERE condition → p.PhoneRaw

The first correct selection is JSON_VALUE(c.ProfileJson, ' $.contact.phone ' ) because the phone number is stored as a scalar JSON property . Microsoft documents that JSON_VALUE extracts a scalar value from JSON text, whereas JSON_QUERY is intended for JSON objects or arrays.

The second selection is REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' ) . The regular expression [^0-9] matches every character that is not a digit . Replacing all such characters with an empty string converts a formatted value such as:

+000 000-000-0000

into:

0000000000000

Microsoft documents that REGEXP_REPLACE returns the source string after occurrences matching the regular expression are replaced, and it applies to Azure SQL Database.

The final blank is p.PhoneRaw , producing:

WHERE p.PhoneRaw IS NOT NULL;

This excludes customers that do not have a phone value while preserving one output row for each customer that does.

The completed query is:

WITH PhoneCTE AS

(

SELECT DISTINCT

c.CustomerId,

JSON_VALUE(c.ProfileJson, ' $.contact.phone ' ) AS PhoneRaw

FROM dbo.Customers AS c

)

SELECT

p.CustomerId,

REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' ) AS PhoneNumerals

FROM PhoneCTE AS p

WHERE p.PhoneRaw IS NOT NULL;

Final drag-and-drop selections:

First target: JSON_VALUE(c.ProfileJson, ' $.contact.phone ' )

Second target: REGEXP_REPLACE(p.PhoneRaw, ' [^0-9] ' , ' ' )

Third target: p.PhoneRaw