Actually I did try it that way first (without the single quotes), i guess
pasted my final attempts. Im not sure how else to code it...This is so
perfect for what I need to do...perhaps I could call a StoredProc since it
does work in T-SQL....
Thanks for all the suggestions so far!
-----Original Message-----
From: Michael Caulfield [mailto:[EMAIL PROTECTED]]
Sent: Saturday, April 07, 2001 7:11 PM
To: CF-Talk
Subject: RE: update numeric SubString?
I would do it this way:
<CFQUERY NAME="USAM4" DATASOURCE="blah">
UPDATE mytable SET
switch = left(switch, 3) + '1' + right(switch , len(switch) -4)
WHERE po = 236107
</CFQUERY>
In other words drop the single quotes from everything except the number
you're swapping, otherwise, what you'll end up with is a string that
literally concatenates your functions into the string (e.g. switch will
equal "left(switch, 3)1right(switch , len(switch) -4)").
I'm not sure why it would give you null though -- it really should bomb.
-----Original Message-----
From: Adrian Cesana [mailto:[EMAIL PROTECTED]]
Sent: Saturday, April 07, 2001 7:12 PM
To: CF-Talk
Subject: RE: update numeric SubString?
hmmmmmmm, I think I have the syntax correct , here is what Ive tried:
<CFQUERY NAME="USAM4" DATASOURCE="blah">
UPDATE mytable SET
switch = 'left(switch, 3)' + '1' + 'right(switch , len(switch) -4)'
WHERE po = '236107'
</CFQUERY>
I also tried this which gives me the same results, <NULL>.
<CFQUERY NAME="USAM4" DATASOURCE="blah">
UPDATE mytable SET
switch = CAST( left( cast(switch AS varchar(11)) , 3) + '1' + right(
cast(switch AS varchar(11)) , len( cast(switch AS varchar(11)) ) -4) AS
Numeric)
WHERE po = '236107'
</CFQUERY>
DB: SQL7
CF: 4.01
Perhaps its ODBC driver related......wierdness
-----Original Message-----
From: Michael Caulfield [mailto:[EMAIL PROTECTED]]
Sent: Saturday, April 07, 2001 5:27 PM
To: CF-Talk
Subject: RE: update numeric SubString?
Actually I just tried it from CF on a test table, and it's working fine (i'm
using CF5 Beta 1, but I don't think that matters). Maybe there was a slight
difference in your syntax? Here's the cf query
<cfquery datasource="dubliners" name="qry" username="sbloom">
USE pubs
UPDATE tbl
SET col =left(col,3)
+ '0'
+ right(col, len(col) - 4)
</cfquery>
pubs is the database, tbl is the table, and col is the column. Zero can of
course be replaced with any digit.
Hope it works --- feel free to post a more complete version of your code if
it doesn't.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
