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.
| Author |
Topic |
|
yogi86
Starting Member
13 Posts |
 Posted - 2011-08-05 : 10:11:24
|
| Hello everyone, hope y'all are having a great day.My problem is hopefully a simple one. It goes something like this:Phone Number (My Records):121497584622149758463214975846412149758465Phone Number (Their Records)12149758462121497584631214975846412149758465My numbers are not always populated with a 1 before the dialed number while my other records all have a 1 in front. I would like to add a 1 in front of any numbers on my records that do not already have a 1 in front. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-05 : 10:19:23
|
What is the data type of your column? If it is BIGINT, do this:case when phone_number < 9999999999 THEN 10000000000 + phone_number ELSE phone_number END If it is character (varchar), then do this:case when len(phone_number) <= 10 then '1' + phone_number ELSE phone_number END |
 |
|
|
yogi86
Starting Member
13 Posts |
Posted - 2011-08-05 : 10:22:27
|
| Thanks Sunita! |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-08-05 : 12:52:26
|
If its varchar, wouldn't this be better (so you don't have a case statement)?right('1'+phone_number,10)Corey I Has Returned!! |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-08-05 : 14:48:27
|
quote:
Originally posted by Seventhnight
If its varchar, wouldn't this be better (so you don't have a case statement)?right('1'+phone_number,10)CoreyI Has Returned!!
Of course it is (at least I think it is). At the very least, it is shorter. But, I was testing to see if you were paying attention!!  |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-08-05 : 16:17:27
|
quote:
Originally posted by Seventhnight
If its varchar, wouldn't this be better (so you don't have a case statement)?right('1'+phone_number,10)
If the number already has a 1 at the front then tuples won't mach, so the CASE expression is probably the better option. |
|
|
|
|
|
|
|
|
Adding Numbers