right string function

[elmo29]

I have a phonenumber field and I want to get the last five digits, so I am using the following

right(phonenumber,5)

This works on most of my tables except one, where I get the following

00000
01
02
03
04
0100

etc

Anyone know what this is all about and why I am getting this instead of 5 digits. I do have phonenumber set as primary key in this table but I cannot see any other differences. I took the primary key off and the same thing happened

The phonenumber field in all tables is nvarchar(25)

Can any one help???

 

[arrowhouse]

Try selecting for example right(phonenumber,5), phonenumber, LEN(phonenumber)... from the table thats giving you problems. What do you get? Are the phone numbers at least 5 characters long?

 

[elmo29]

All phonenumbers are 11 characters. I get for example:

00000,11,01212100000
01, 11, 01212100001
02, 11, 01212100002
010, 11, 01212100010

if I do a left(phonenumber,5) I always get 5 digits eg '012121'

it seems very bizarre......

if I do len(right(phonenumber,5)) I dont get 5 back

Any help??

 

[bperry]

Could you try RIGHT(RTRIM(phonenumber),5)?


If that doesn't work, perhaps try this and post the results here.

Select
phonenumber as fullphone,
LEN(phonenumber) as lenfullphone,
LEFT(ltrim(rtrim(phonenumber)),5) as leftphone,
RIGHT(ltrim(rtrim(phonenumber)),5) as rightphone
From yourTable

 

[arrowhouse]

Try select right(RTRIM(phonenumber),5) you may have trailing blanks (which LEN doesnt count).

 

[elmo29]

right(rtrim(phonenumber),5) works!!! hadnt realised there were spaces

thank you!!

 

[betaanish]

I think this has to do something with installation options.
cause the same works on my m/c.