Find and replace in acces query

[Kan09]

I have a table with a single column that contains various data.
Among that data, certain rows contain characters that need to be replaced.
ex: order number: 8651124563357 (the numbers are different for every row)
I need to find all the rows that contain "order number: "and replace 6 characters starting with the 19th:

ex: order number: 86511******57

How do i build that expression?

Thank you

[Rabbit]

You can use the Replace function. Or you can use a combination of the Left and Right functions and concatenate it.

[Kan09]

the data in the table's column looks like this:

ColumnHeader
00:39:43 <- END
08:21:19 OPERATOR DOOR OPENED
08:21:30 OPERATOR DOOR CLOSED
08:21:43 OPERATOR DOOR OPENED
08:21:45 OPERATOR DOOR CLOSED
08:21:58 -> START
08:21:58 order number: 8651124563357

i need the query to check every row and if the row contains "order number :" in it, concatenate only part of the number (first six and last 2).

I don't know how to define the search to use wild cards. I thought of using a iif function with the left and right nested functions, but like i said i don't know how to write the wild cards in the search string and i'm thinking that the replace function would be more suitable for the operation.

[twinnyfo]

Kan09,

Is this a one time query or something that will have to run frequently?

Either way, one option would be to build a VBA sub to create a recordset, then cycle through the records, check the contents with Like ("*order number*"). If it meets the criteria, update the record with a concatenation of the order number with the masking. One key would b that all of the order numbers would have to be he same number of characters.

It is an interesting and unorthodox table that you have, in terms of the typees of data you have in it, which also begs the question why the masking of the order number after the fact? You should either mask it when it goes in, or only maskit when it is on a form.

Perhaps more explanation is required before we can provide any further guidance.

[Mariostg]

This should get you started:

SELECT tmp_tbl.columnHeader, InStr([columnHeader],"r:") AS loc, 
Left([columnheader],[loc]+2) & Mid([columnheader],
[loc]+3,6) & "******" & Right([columnheader],2) AS target
FROM tmp_tbl
WHERE (((tmp_tbl.columnHeader) ALike "%Order number%"));
 

You may need to replace the % character with * in the WHERE clause.