Remove part of a string...

[RollingMoose]

We have concatenated a large number of fields and records, about 350 and 700,000 respectively. What we ended up with is a field that contains data such as DF15ED20WS15. What we need to do is remove one of the data elements that was concatenated. For example, if we have data as shown above we want to remove all occurrences of ED20 and end up with DF15WS15. Any suggestions or advice is appreciated. Thanks.

 

[cghoga]

Is the string you want to parse out always the middle 4 characters?

If so you could run a query to take the left 4 characters of the field then take the right 4 characters and concatenate those 2 together.

The expression in query design would look like this:

Expr1: Left([FieldName],4) & Right([FieldName],4)

Instead of removing the data you would be creating another field. Then after the new field is created you can delete the old field.

Hope this helps.

 

[RollingMoose]

Actually, the string to remove could be located anywhere in the field which has as many as 78 characters in it. I wish it was always in the same location though...Thanks for the reply.

 

[cghoga]

Wow! This is a tough one.

Try this...

Use the * wildcard with Like to find all the records with a specific string.

For example:

Select fieldname from tablename where fieldname like "*" & "ED20" & "*"

If you wanted to have Access prompt you for the value of the string so you would have to adjust it each time it would look like this:

Select fieldname from tablename where fieldname like "*" & [EnterValue] & "*"

Then, using that recordset that is created from the above query, use the Instr function to parse out the values you don't want and leave you with the values you do want.

If you wanted to take out ED20 from DF15ED20WS15 then it would look like this if you created an expression in your query:

NewValue: Left$([FieldName],InStr(1,[FieldName],"ED20&quot+1) & Right$([FieldName],Len([FieldName])-InStr(1,[Fieldname],"ED20&quot-1)

Be sure to replace FieldName with the appropriate field name in your table.

You would need to run the queries separately.

First run the query that finds all records with the specified string.
Then, run the query that uses the Instr function.

Hope this helps.

 

[bboffin]

Run the query with a WHERE clause such as TextField LIKE "*ED20*"

This will ensure that you are only operating on valid records.

If you are willing to write a function in VBA the solution is simple:

Public Function fnStripText(sValue As String, sStrip As String) As String
Dim p As Integer
p = Instr(sValue, sStrip)
fnStripText = Left(sValue, p-1) & Mid(sValue, p + Len(sStrip))
End Function

Then in your update query assign the value:

fnStripText([TextField], "ED20&quot

to the TextField column.

In SQL the code for the new value for TextField would look something like:

Left([TextField], Instr([TextField],"ED20") - 1) & Mid([TextField, Instr([TextField],"ED20") + 4)

 

[raskew]

Here's a little more generic technique that may come in handy in a variety of scenarios. Try copying / pasting in a new module then, from the debug window, type:
x = "DF15ED20WS15"
? xreplaceit(x, "15", "&quot
DFED20WS
-or-
? xreplaceit(x, "ED20", "&quot
DF15WS15

Public Type xSec
   LeftS As String
   MidS As String
   RightS As String
   Total As String
End Type

Function GetXSec(pStr As String, pDelim As String) As xSec
   GetXSec.LeftS = Left(pStr, InStr(pStr, pDelim) - 1)
   GetXSec.MidS = Mid(pStr, InStr(pStr, pDelim), Len(pDelim))
   GetXSec.RightS = Mid(pStr, InStr(pStr, pDelim) + Len(pDelim))
   GetXSec.Total = GetXSec.LeftS + GetXSec.MidS + GetXSec.RightS
End Function

Function xReplaceIt(ByVal xpstr As String, xpdelim As String, xnew As String) As String
'*******************************************
'Name:      xReplaceIt (Function)
'Purpose:   Test of using user-defined types
'           as described by Allen Browne at
'           [URL unfurl="true"]http://members.iinet.net.au/~allenbrowne/ser-16.html[/URL]
'Inputs:    ? xreplaceit("The quick brown fox jumped over the dog", "o", "~")
'Output:    The quick br~wn f~x jumped ~ver the d~g
'*******************************************

Dim MyStr As String

MyStr = xpstr
Do While InStr(MyStr, xpdelim) > 0
   MyStr = GetXSec(MyStr, xpdelim).LeftS + xnew + GetXSec(MyStr, xpdelim).RightS
Loop
xReplaceIt = GetXSec(MyStr, xnew).Total
End Function

HTH - Bob

 

[CajunCenturion]

This would be a good use of Regular Expressions if you care to go that route.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein