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
