assuming all rows are at least 4 places long:
update tbl
SET col =left(col,3)
+ '0'
+ right(col, len(col) - 4)
In SQL 2000 I can do this without any explicit conversion of types, I don't
know if you'd have to cast it to a string and back to a number in another
system or not.
If some entries have less than 4 places, just add a
WHERE col > 999
to it.
HTH,
Michael Caulfield
-----Original Message-----
From: Adrian Cesana [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 7:27 PM
To: CF-Talk
Subject: update numeric SubString?
If I have a numeric field that is '99999999999', is there any way to just
update the forth number, something like this although I know it does not
work....
UPDATE blah SET SubString(Switch, 4,1) = '1' WHERE blah
I need to do this to several records and will not know the entire value of
the Field prior to updating. I know I can do this thru a loop but it gets
really ugly when your dealing with several thousands of records.
Thanks,Adrian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
