Tricky Data Conversion ....

[elmo29]

I have a phonenumber field stored as nvarchar. I need to cycle through these numbers incrementing them by one each time. My original idea was to convert them to an int and then add one to the int ... but for phone numbers starting with 0 like 01923 .... it gives me 1923, the phonenumbers are never going to be a fixed length of characters, they will most likely to be between 5 and 25.

What I am now planning on doing is finding the len of the phone number and if it is one more than the integer conversion of it, add a 0 to the start. But to me this seems a bit clumsy ... is there any better way? i.e. can I somehow store the phonenumber as a number and still keep the 0 at the start. Or can I somehow, based on the len of the field define a format for it ... like visual basic?

Hope you can help

 

[jnicho02]

elmo,
Why not take the last few digits with a [tt]RIGHT(phoneNumber,3)[/tt], add a number to that and glue it back on to the end with a [tt]SUBSTRING[/tt] to handle leading zeros?

This would leave the start of the phone number alone.

something like...(try this on the pubs database)
[tt]SELECT phone,
[tab]LEFT(phone,LEN(phone)-3) +
[tab]SUBSTRING("000",1,3-LEN(convert(char(3),convert(int,RIGHT(phone,3))+1))) +
[tab]convert(char(3),convert(int,RIGHT(phone,3))+1)
from authors[/tt]

The space padding line is a bit clumsy, maybe someone can suggest a shorter line...

 

[elmo29]

I was getting so caught up in the problem I just couldn't see another solution. Your idea sounds much more sensible than mine )

Thanks a lot!!