How to update a string by inserting characters in between

Question

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

Take that SELECT and rewrite as an UPDATE query. The function you wrote with `LEFT()` `RIGHT()` and `MID()` will be the same. — JNevill, Jul 29 '19 at 16:45

score 1 · Accepted Answer · answered Jul 29 '19 at 16:46

1

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);

answered Jul 29 '19 at 16:46

Gordon Linoff

1,242,037
58
646
786

I was unaware of the mid function. Thanks! – honey_badgerzz Jul 29 '19 at 16:59

How to update a string by inserting characters in between

1 Answers1