This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Some SQL help please

 
Luke Chadwick
Greenhorn
Posts: 8
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
ben riches
Ranch Hand
Posts: 126
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you the luke chadwick who use to play united?
 
Luke Chadwick
Greenhorn
Posts: 8
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And I aint heard that before.
 
Rick Portugal
Ranch Hand
Posts: 243
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 8
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 263
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic