Try:
select "(" & left(Str(telephone), 3) & ") " & mid(Str(telephone), 4, 3) &
"-" & right(Str(telephone), 4)
from ...
Just coding off of the top of my head, so definitely no guarantees. The
idea is that the Str function will eliminate the decimal point. If if
doesn't ,try:
select "(" & left(Str(telephone), 3) & ") " & mid(Str(telephone), 4, 3) &
"-" & mid(Str(telephone), 7,4)
from ...
Steve
-------------------------------------
Steven Monaghan
Oracle DBA / Cold Fusion Developer
MSC Industrial Direct Co., Inc.
Melville, NY
[EMAIL PROTECTED]
http://www.mscdirect.com
-------------------------------------
-----Original Message-----
From: Janine Jakim [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 04, 2001 12:54 PM
To: CF-Talk
Subject: RE: SQL quicky
Yes I am trying to format a phone number (it's kept in the database as
1234567890) for an output form. The problem is that the usual formatting
(<CFSET TELEPHONE="(#left(SchoolInfo.TELEPHONE,3)#)
#mid(SchoolInfo.TELEPHONE,4,3)#-#mid(SchoolInfo.TELEPHONE,7,4)#">)won't work
for this output so I'm trying to move the formatting to inside the sql
query.
This formats fine: Gives the result: (123)456-7890
<CFOUTPUT QUERY= "1">
#TELEPHONE#
</CFOUTPUT>
BUT I need it to loop through a 2ndary query and this does not work: Gives
result 1234567890.0
<CFOUTPUT QUERY="1">
<CFLOOP QUERY="2ndary">
#TELEPHONE#
</CFLOOP>
</CFOUTPUT>
Someone sent me the code <CFOUTPUT><cfset foo = insert(") ","1234567890",3)>
(#insert("-",foo,6)#</cfoutput> but this just gives me a result of
(123456-7890.0
and I'm not sure how to get the other ) and how to get rid of the decimal
Anyways I find it all kind of weird that that is the only formatting that is
screwy- all the others (ie: city, state zip) etc are fine.
Any ideas?
-----Original Message-----
From: Stephen Hait [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 03, 2001 3:16 PM
To: CF-Talk
Subject: RE: SQL quicky
Sorry, I don't understand what you're trying to accomplish here.
Are you trying to output a value obtained from a query to a web
page or add formatted data to a database table or something
else?
> That's what I had originally. It works fine for my forms that pull
> up individual records. However, upon doing the bulk records I needed
> to change how my queries sat. All the related queries are a <CFLOOP
> QUERY> within a query.( <CFQUERY MAIN><CFLOOP QUERY1><CFLOOP
> QUERY2><CFLOOP QUERY3> </CFQUERY>) And for some reason the
> formatting of telephone now will not <CFSET>.... I'm not sure why,
> so I thought I'd try to format it within the sql statement instead.
>
> -----Original Message-----
> From: Stephen Hait [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, October 03, 2001 2:14 PM
> To: CF-Talk
> Subject: RE: SQL quicky
>
>
> This is not exactly within a cf sql statement, but...
>
> If you can be sure that your phone numbers are 10 digit strings you
> can format your output like this:
>
> <cfset tel="8005551212">
>
> <cfoutput>tel=(#left(tel,3)#)
> #mid(tel,4,3)#-#right(tel,4)#</cfoutput>
>
> The output would look like this:
> tel=(800) 555-1212
>
> > How do I format a phone number (###) ###-#### within my cf sql
> > statement? My backend is sql 2000. Thanks in advance.
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
