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 |
|
Blessed1978
Yak Posting Veteran
97 Posts |
 Posted - 2014-04-10 : 16:56:31
|
| SQL TO update my telelephone numbers in a column named NUMBERS from 4098887777 to (409)888-7777 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-10 : 17:05:08
|
| Is the datatype of that column a "string" data type? Also, can I ask you would want to do this? It seems like it would be better to format the text in the frontend rather than in SQL. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-10 : 17:09:06
|
If you really want to do it in SQL maybe this will help:DECLARE @Foo VARCHAR(20) = '4098887777';SELECT FORMAT(CAST(@Foo AS BIGINT), '(###)###-####') |
|
|
|
Blessed1978
Yak Posting Veteran
97 Posts |
Posted - 2014-04-10 : 18:08:58
|
| ok but WHEN I DO A SELECT FORMAT(CAST(TELEPHONE AS BIGINT), '(###)###-####') FROM Logs_Raw where exists (select HomePhone from CUSTOMERS ) I RECEIVE A ERROR Msg 8114, Level 16, State 5, Line 2Error converting data type varchar to bigint. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-10 : 18:26:50
|
Then I guess you have have bad data or it is not all formatted like "4098887777" like your post says. Perhaps you have other non-numeric characters in your table?Maybe this will help:SELECT *FROM <YourTable>WHERE TELEPHONE LIKE '%[^0-9]%' |
|
|
|
Blessed1978
Yak Posting Veteran
97 Posts |
Posted - 2014-04-10 : 19:13:20
|
| Yes I have a like 14984443332 more than 9 characters I also have 432143212. So not all the numbers are 9 characters thanks for assisting |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-11 : 11:29:04
|
quote:
Originally posted by Blessed1978
Yes I have a like 14984443332 more than 9 characters I also have 432143212. So not all the numbers are 9 characters thanks for assisting
So how do those fit into the format you have prescribed? Or do you only want to format numbers that are 8 characters long? |
|
|
|
|
|
|
UPDATE TELEPHONE NUMBERS