adventureworks - SQL Server Adventure Works Person.PersonPhone Table -

adventureworks - SQL Server Adventure Works Person.PersonPhone Table -

i have question person.personphone table in adventureworks2012, , related question sql.

firstly, in table there phone number shown 55-2555-0100, appears typo; presumably intended 552-555-0100, match pattern of other phone numbers. wondering if confirm error.

secondly, suppose want determine 3-digit area codes not appear in phonenumber column. 1 way utilize dbo.nums table itzik ben-gan's tsql2012 database, has 1 column, n, containing integers 1 100,000. so, example, next query works:

(1) select n dbo.nums n >= 100 , n < 1000 , n not in (select substring(p.phonenumber, 1, 3) person.personphone p substring(p.phonenumber, 1, 3) '[1-9][0-9][0-9]');

however, next query fails:

(2) select n dbo.nums n >= 100 , n < 1000 , n not in (select substring(p.phonenumber, 1, 3) person.personphone p p.phonenumber '[1-9][0-9][0-9]%');

the error "conversion failed when converting nvarchar value '1 (' info type int."

there phone-numbers in table of form '1 (11) xxx', should ignored purposes of exercise. apparently it's trying compare n dbo.nums these, though subquery excludes them. (the subquery in (2), when executed separately, has same result subquery in (1)).

even stranger, if (2) modified doing has no effect (e.g. replace empty string empty string in each phonenumber), query works:

(3) select n dbo.nums n >= 100 , n < 1000 , n not in (select substring(replace(p.phonenumber, '', ''), 1, 3) person.personphone p p.phonenumber '[1-9][0-9][0-9]%');

so why (2) fail (3) works?

thanks,

mark brodie

i can't explain why info may or may not typo, sample , reminder programme around potential info issues carefully.

as (2) vs (3), error due implicit conversions. in both (2) , (3), using n not in (<subquery>), implicitly forcing elements returned subquery converted int comparison, , error message elements subquery cannot converted int (as case error message phone numbers origin "1 (".

what interesting why (3) works (2) not. both work alter n not in convert(varchar(10), n). query (2) attempts implicit conversion int each element after substring before clause applied, , generates error. in query (3), having replace first modifies execution (and execution plan confirms this) in applied before implicit conversion int occurs on result set.

sql-server-2012 adventureworks