Replace in the middle of a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a set of characters that I need to replace two of them in the center.
The two that needs to change are always in the same positions. How can I
change them? For example:

1000000009557142070200000000000387010

I need to change the 02 in positions 19 & 20 to a 01.

Thank you!
 
You should be able to use an expresssion like the following


LEFT([YourField],18) & Replace([YourField],"02","01",19,1,1)


In a query you could also use
UPDATE YourTable
SET [YourField] = LEFT([YourField],18) & "01" & Mid([YourField],21)
WHERE [YourField] Like "??????????????????02*"


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Substitute your field name for [y] in the expression.

Left([y],18) & IIf(Mid([y],19,2)="02","01",Mid([y],19,2)) &
Right([y],Len([y])-20)
 
Back
Top