Hello, im fairly new to SQL and believe it or not I have a problem. Im trying to update the first character value of each field in a column. e.g. "AZZZ999" would become "BZZZ999" the lengths of field value change. I would appreciate any help with this. In the end I want to put this into Java (using JDBC), but for now need to know the syntax for the SQL. And then the updating would be automatic, e.g. a user clicks upon a button the update is initiated. Cheers Luke
What database are you using? Oracle? MySQL? Also, I'm not sure what you mean by "each field in a column". Isn't a field the same thing as a column? And what do you mean by "the lengths of field value change"?
IBM 286, SCJP, SCWCD, EIEIO
Joined: Mar 05, 2004
Hello, Im using Access 2000, at the moment but will move into java (hopefully). Sorry it was went to be each row in a column. Basically when the update is made the first character in the row will increment e.g. from "A978890" to "B978890" and then the next one will be "C978890" and when Z is reached, it reverts back to A again. The size of each row can change, e.g. 6 or 8 characters long. Hope this makes a bit more sense, any kind help with this would be greatly apprecaited.
Not quite sure what you are asking for, but here is an Access query that will take the first letter of a field and increment it. It assumes that the first letter in the field is uppercase A-Z.
It works by taking the first character in the field, converting it to an ASCII value, offsetting it to 0 and adding one (magic number 64). It then takes the modulus of this with 26 (the number of letters) which does the wrapping of "Z" back to "A", and finally adds 65 to offset it back into the correct ASCII capital letter range and converts it back to an ASCII character. The last thing it does is add back on the remainder of the original field. Hope this helps, Tom Blough
Tom Blough<br /> <blockquote><font size="1" face="Verdana, Arial">quote:</font><hr>Cum catapultae proscriptae erunt tum soli proscripti catapultas habebunt.<hr></blockquote>