Trying to compare a phone number

Transact-SQL
1

Evening Guys,

I can't for the life of me work out why this isn't working...

Declare @testnum nvarchar = '447123456789'

SELECT T_TID
FROM Employee WHERE
(REPLACE(Employee.Phone_Mobile, ' ', '')) = ('0' + (Right(@testnum, 10)))

As you can see i'm trying to compare 2 phone numbers 1 is written:

"01234 123456"

The Second is written:

"441234123456"

Any idea what I'm doing wrong?

Thanks

Dave

2

Hi Dave,

1 - I had to add a length "(100") the DECLARE @testnumber nvarchar
2 - I took the left and right of the "=" and moved them to the SELECT line (to see the output)
3 - I replaced your table with a virtual table

This is what I got:

Declare @testnum nvarchar(100) = '447123456789'
SELECT T_TID, REPLACE(Employee.Phone_Mobile, ' ', '') AS A, (('0' + Right(@testnum, 10))) AS B
FROM (
SELECT 1 AS T_TID,
'01234 123456' AS Phone_Mobile
) AS Employee

A = 01234123456
B = 07123456789

So, in the declaration of @testnum you have '447123456789' but you also say "The Second is written: "441234123456". If @testnum is set to '441234123456' it works....

Hopefully this will helps :wink:

3

Hi Rnoldz,

That it, spot on thank you...!!

Many thanks

Dave

4

I wish SQL would not allow this, or would warn. It declares @testnum as the default length of 1 - absolutely useless :frowning:

Of course there are other times when it uses a different default length ... even more useless.

Important to ALWAYS use a length definition when declaring (N)Varchar, but SQL will never tell you if you forget :frowning: