Search code examples
sqlms-accessjet-sql

How to update a string by inserting characters in between

In MS-Access, I have a text field labeled idStr and currently, the data shows like this "000000000". I would like to transform the string by writing an update query and making it like this: "000-00-0000". In other words, I'd like to insert - (hyphen) after the third character and after the fifth character.

The only solution I've seen is to write a select query using left and right but I'm actually attempting to update the string and make it permanent

Solution

  • If you want to change the string, you can put the logic in a update statement:

    update t
    set idstr = left(idstr, 3) & "-" & mid(idstr, 4, 2) & "-" & right(idstr, 4);