• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Some SQL help please

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are you the luke chadwick who use to play united?
 
Luke Chadwick
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And I aint heard that before.
 
Ranch Hand
Posts: 243
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 263
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
reply
    Bookmark Topic Watch Topic
  • New Topic