Dave's certainly right, substring can help, but you'll need to cast
the field over to some sort of text field before you use it. This
should get you started:
SELECT top 1
SUBSTRING(CAST(1234123412341234 AS varchar(16)), 1, 4) + '-' +
SUBSTRING(CAST(1234123412341234 AS varchar(16)), 5, 4) + '-' +
SUBSTRING(CAST(1234123412341234 AS varchar(16)), 9, 4) + '-' +
SUBSTRING(CAST(1234123412341234 AS varchar(16)), 13, 4)
AS formattedNumber
FROM contact
....replace 1234123412341234 with the name of whatever column the
number is in, etc.
-joe
----- Original Message -----
From: Tony Weeg <[EMAIL PROTECTED]>
Date: Fri, 2 Jul 2004 12:23:29 -0400
Subject: RE: sql question
To: CF-Talk <[EMAIL PROTECTED]>
t'is not a credit card number, but that's ok, doesn't matter.
really its just for readability sake on a printed label.
:) so, basically im working with an old label making program, formatting
some data, sending it to a csv, odbc on the client pc, to the csv file,
and then the label program works from the csv, I need to get this in
this way each time, and still store the Number in my sql database, as a
number, not as a varchar...
make sense?
need quick and dirty way, on the fly, each time, to format those
numbers, or don't do it at all, and tell the higher ups, I cant ;)
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
visit www.antiwrap.com to send long url emails to your friends!
-- dont mistake my perfection as arrogance
anonymous
-----Original Message-----
From: Ryan Duckworth [mailto:[EMAIL PROTECTED]
Sent: Friday, July 02, 2004 12:07 PM
To: CF-Talk
Subject: RE: sql question
>>is there a function in transact sql that allows something like the
cfmx insert() function?
To display it, just use mid and call your CFC function that returns the
decrypted CC number.
#mid(objCreditCard.getCcNumberDecrypted(),1,4)#-#mid(objCreditCard.getCc
NumberDecrypted(),5,4)#-#mid(objCreditCard.getCcNumberDecrypted(),9,4)#-
#mid(objCreditCard.getCcNumberDecrypted(),13,4)#
If you want to convert all of them from 1234567891234567 to
1234-5678-9123-4567 I would just:
1. Change the type of your DB field
2. Use a regular _expression_ to add the dashes
3. Run an update on each Credit Card number. Even with 10,000 records,
CF would blaze through the list in a few minutes.
Note: Amex and a few other cards have different lengths, so be careful
Ryan Duckworth
Macromedia Coldfusion Certified Professional
Uhlig Communications
10983 Granada Lane
Overland Park, KS 66207
(913) 754-4272
-----Original Message-----
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Friday, July 02, 2004 10:42 AM
To: CF-Talk
Subject: sql question
is there a function in transact sql that allows something like
the cfmx
insert() function?
I have a number field, 1236547852145632, that I want to display
like:
1236-5478-5214-5632
and don't want to do this...
<cfset myNum = 4103346331>
<cfloop from = 4 to = #len(myNum)# index = i step = 5>
<cfset myNum = insert('-',myNum,i)>
</cfloop>
too many times in a 10000 query recordset :)
if I can pass it off to sql server, GREAT!
thanks.
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
visit www.antiwrap.com to send long url emails to your friends!
-- dont mistake my perfection as arrogance
anonymous
_____________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
