my dog learned polymorphism*
The moose likes JDBC and the fly likes Some SQL help please Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Some SQL help please" Watch "Some SQL help please" New topic

Some SQL help please

Luke Chadwick

Joined: Mar 05, 2004
Posts: 8
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.
ben riches
Ranch Hand

Joined: Nov 08, 2002
Posts: 126
Are you the luke chadwick who use to play united?
Luke Chadwick

Joined: Mar 05, 2004
Posts: 8
And I aint heard that before.
Rick Portugal
Ranch Hand

Joined: Dec 17, 2002
Posts: 243
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"?

Luke Chadwick

Joined: Mar 05, 2004
Posts: 8
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.
Tom Blough
Ranch Hand

Joined: Jul 31, 2003
Posts: 263
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>
I agree. Here's the link:
subject: Some SQL help please
Similar Threads
Recordset Get Fields
Update Database with the newly entered value
converting the date/time fields into Strings
Need help with a SQL statement
Connecting with MYSQL Database