Adventure Works Person.PersonPhone Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adventure Works Person.PersonPhone Table

Author

Topic

brodiemark
Starting Member

1 Post

Posted - 2014-11-02 : 18:30:23

I have a question about the Person.PersonPhone table in AdventureWorks2012, and a related question about SQL.

Firstly, in the table there is a phone number shown as 55-2555-0100, which appears to be a typo; presumably it was intended to be 552-555-0100, which would match the pattern of the other phone numbers. I was wondering if anyone could confirm that this is an error.

Secondly, suppose we want to determine which 3-digit US area codes do not appear in the PhoneNumber column.
One way to do this is to use the dbo.Nums table from Itzik Ben-Gan's TSQL database, which has one column, n, containing the integers from 1 to 100,000. So, for example, the following query works:

(1)
SELECT n
FROM dbo.Nums
WHERE n >= 100 AND n < 1000 AND n NOT IN
(SELECT SUBSTRING(P.PhoneNumber, 1, 3)
FROM Person.PersonPhone AS P
WHERE SUBSTRING(P.Phonenumber, 1, 3) LIKE '[1-9][0-9][0-9]');

However, the following query fails:

(2)
SELECT n
FROM dbo.Nums
WHERE n >= 100 AND n < 1000 AND n NOT IN
(SELECT SUBSTRING(P.PhoneNumber, 1, 3)
FROM Person.PersonPhone AS P
WHERE P.PhoneNumber LIKE '[1-9][0-9][0-9]%');

The error is "Conversion failed when converting the nvarchar value '1 (' to data type int."

There are phone-numbers in the table of the form '1 (11) xxx', which should be ignored for the purposes of this exercise. Apparently it's trying to compare the n from dbo.Nums to these, even though the subquery excludes them. (The subquery in (2), when executed separately, has exactly the same result as the subquery in (1)).

Even stranger, if (2) is modified by doing something that has no effect (e.g. replace empty string with empty string in each PhoneNumber), the query suddenly works:

(3)
SELECT n
FROM dbo.Nums
WHERE n >= 100 AND n < 1000 AND n NOT IN
(SELECT SUBSTRING(REPLACE(P.PhoneNumber, '', ''), 1, 3)
FROM Person.PersonPhone AS P
WHERE P.PhoneNumber LIKE '[1-9][0-9][0-9]%');

If anyone could explain why (2) fails but (3) works it would be greatly appreciated!

Thanks,

Mark Brodie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources