Delect portions of a Phone Number from a field called PhoneNumber

I want to select portions of a Phone number from a field called PhoneNumber, the numbers come in like example

• (343)765-58584
• (343)765-6544
• (343)765-6758
• etc

The numbers have same pattern just like this. So I want to select three different portions from this number, namely: countryCode, AreaCode and the Number, even though the numbers do not contain the country code but I will like to add that as field just incase. This what am expecting from the output. The right output:

Please try the following solution based on tokenization via SQL Server's XML and XQuery functionality.

Notable points:

• CROSS APPLY is tokenizing PhoneNumber column as XML.
• Using XPath predicate /root/r[1]..., etc. we are retrieving 1st, 2nd, and 3rd tokens as required parts of a phone number.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, PhoneNumber VARCHAR(200));
INSERT @tbl VALUES
('(343)765-58584'),
('(343)765-6544'),
('(343)765-6758'), 
('+972(305)765-6758');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ')';

SELECT t.*
    , c.value('(/root/r[1]/text())[1]', 'VARCHAR(10)') AS CountryCode
    , c.value('(/root/r[2]/text())[1]', 'CHAR(3)') AS AreaCode
    , c.value('(/root/r[3]/text())[1]', 'VARCHAR(20)') AS PhoneNumber
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
    REPLACE(REPLACE(PhoneNumber,'(',@separator), @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML)) AS t1(c);

Output