James,
I had the same problem. I was picking up a phone number from a flat file and
getting a float out of it. Most annoying.
I ended up with a function in TSQL. You can modify this to
use simialar padding on the "area code" as I did on the number.
/***************************************************************
[dbo].[PhoneNumberToString]
Parses a phone number out of a float.
****************************************************************/
CREATE FUNCTION [dbo].[PhoneNumberToString] (@PhoneNumber float)
--RETURNS FLOAT
RETURNS VARCHAR(20)
AS
BEGIN
IF @PhoneNumber = 0.0
RETURN ''
DECLARE @AREA VARCHAR(3)
DECLARE @PREFIX VARCHAR(3)
DECLARE @NUMBER VARCHAR(20)
DECLARE @PREFIX_PORTION_OF_PHONE_NUMBER FLOAT
DECLARE @AREA_PORTION_OF_PHONE_NUMBER FLOAT
DECLARE @PHONE_NUMBER_WITHOUT_AREA FLOAT
DECLARE @PHONE_NUMBER_WITHOUT_PREFIX FLOAT
SELECT @AREA = ''
SELECT @PREFIX = ''
SELECT @NUMBER = ''
SELECT @AREA = LEFT(CAST(@PhoneNumber/10000 AS VARCHAR(20)), 3)
--RETURN @AREA
SELECT @AREA_PORTION_OF_PHONE_NUMBER = CAST(@AREA AS FLOAT) * 10000000
SELECT @PHONE_NUMBER_WITHOUT_AREA = @PhoneNumber -
@AREA_PORTION_OF_PHONE_NUMBER
SELECT @PHONE_NUMBER_WITHOUT_AREA = @PHONE_NUMBER_WITHOUT_AREA * 1000
SELECT @PREFIX = LEFT(CAST(@PHONE_NUMBER_WITHOUT_AREA/10000 AS
VARCHAR(20)), 3)
--RETURN @PHONE_NUMBER_WITHOUT_AREA
SELECT @PREFIX_PORTION_OF_PHONE_NUMBER = CAST(@PREFIX AS FLOAT) *
10000000
--RETURN @PREFIX_PORTION_OF_PHONE_NUMBER
SELECT @PHONE_NUMBER_WITHOUT_PREFIX = @PHONE_NUMBER_WITHOUT_AREA -
@PREFIX_PORTION_OF_PHONE_NUMBER
--RETURN @PHONE_NUMBER_WITHOUT_PREFIX
SELECT @NUMBER = CAST(@PHONE_NUMBER_WITHOUT_PREFIX / 1000 AS
VARCHAR(20))
IF LEN(@NUMBER) = 3
SELECT @NUMBER = '0' + @NUMBER
IF LEN(@NUMBER) = 2
SELECT @NUMBER = '00' + @NUMBER
IF LEN(@NUMBER) = 1
SELECT @NUMBER = '000' + @NUMBER
RETURN @AREA + '-' + @PREFIX + '-' + @NUMBER
END
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305621
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
