Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Phone number formatting Expand / Collapse
Author
Message
Posted Tuesday, September 8, 2009 6:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 23, 2013 7:44 AM
Points: 40, Visits: 68
Hello,

Please let me know how to retain the + symbol and truncate the decimals

from the following. i tried with round and celing but of no use.

The datatype is varchar.

Example 1: +9966557785.0000000000000000

Example 2 : +9999665545.0000000000000000


Regards,
Sirish
Post #784175
Posted Tuesday, September 8, 2009 6:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 1,949, Visits: 8,315
Possibly the easiest way would be
select convert(bigint,+9966557785.0000000000000000)

Depends on what you want to do with the number after this really though.




Clear Sky SQL
My Blog
Kent user group
Post #784178
Posted Tuesday, September 8, 2009 6:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 23, 2013 7:44 AM
Points: 40, Visits: 68
SELECT SUBSTRING(phonenumber,1,charindex('.',phoneNumber)-1) from table
Post #784191
Posted Tuesday, September 8, 2009 1:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:51 PM
Points: 11,264, Visits: 13,023
Sirish likely has the best answer for fixing currently incorrect data, the real issue is to fix the data entry application.

Another option in SQL Server if the # of characters to the left of the decimal are consistent is this:

SELECT Left('+9999665545.0000000000000000', 11)





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #784550
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse