Re: OT: Transact-SQL number formatting
Phillip, that worked great, thanks!
On 6/19/07, Phillip Ciske [EMAIL PROTECTED] wrote:
T-SQL has a replicate() function that can help. You can pad the last
four digits with zeros using:
REPLICATE('0', 4 - LEN(h.phonenbr)) + CAST(h.phonenbr AS varchar)
So 789 becomes 0789 and 23 becomes 0023.
Phillip
On 6/19/07, Rick Root [EMAIL PROTECTED] wrote:
I'm accessing some mainframe data where phone number parts are stored
as integers. i'm trying to return it as a single formatted number so
I did this:
CAST(H.AREACODE AS VARCHAR)+'.'+CAST(H.PHONEXCH AS
VARCHAR)+'.'+CAST(H.PHONENBR AS VARCHAR) AS PHONE_NUMBER,
which returns 123.456.7890
This is fine as long as the numbers are = 100 or =1000 (for the four
digit part).
If not, I get results like this: 123.456.789 (where the last 4 digits
are actually 0789)
Easy enough to deal with in Coldfusion using numberformat... but I
can't seem to find an equivalent Transact-SQL function, which
surprises me a bit.
Is there a SQL solution for this?
Rick
~|
Upgrade to Adobe ColdFusion MX7
The most significant release in over 10 years. Upgrade see new features.
http://www.adobe.com/products/coldfusion?sdid=RVJR
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281597
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
OT: Transact-SQL number formatting
I'm accessing some mainframe data where phone number parts are stored as integers. i'm trying to return it as a single formatted number so I did this: CAST(H.AREACODE AS VARCHAR)+'.'+CAST(H.PHONEXCH AS VARCHAR)+'.'+CAST(H.PHONENBR AS VARCHAR) AS PHONE_NUMBER, which returns 123.456.7890 This is fine as long as the numbers are = 100 or =1000 (for the four digit part). If not, I get results like this: 123.456.789 (where the last 4 digits are actually 0789) Easy enough to deal with in Coldfusion using numberformat... but I can't seem to find an equivalent Transact-SQL function, which surprises me a bit. Is there a SQL solution for this? Rick -- CFMBB - Coldfusion Message Boards, Version 1.21 Now Available! http://www.cfmbb.org ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281552 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: OT: Transact-SQL number formatting
T-SQL has a replicate() function that can help. You can pad the last
four digits with zeros using:
REPLICATE('0', 4 - LEN(h.phonenbr)) + CAST(h.phonenbr AS varchar)
So 789 becomes 0789 and 23 becomes 0023.
Phillip
On 6/19/07, Rick Root [EMAIL PROTECTED] wrote:
I'm accessing some mainframe data where phone number parts are stored
as integers. i'm trying to return it as a single formatted number so
I did this:
CAST(H.AREACODE AS VARCHAR)+'.'+CAST(H.PHONEXCH AS
VARCHAR)+'.'+CAST(H.PHONENBR AS VARCHAR) AS PHONE_NUMBER,
which returns 123.456.7890
This is fine as long as the numbers are = 100 or =1000 (for the four
digit part).
If not, I get results like this: 123.456.789 (where the last 4 digits
are actually 0789)
Easy enough to deal with in Coldfusion using numberformat... but I
can't seem to find an equivalent Transact-SQL function, which
surprises me a bit.
Is there a SQL solution for this?
Rick
--
CFMBB - Coldfusion Message Boards, Version 1.21 Now Available!
http://www.cfmbb.org
~|
ColdFusion 8 beta â Build next generation applications today.
Free beta download on Labs
http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281570
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
