Search within a string and return text between two characters

[Marc T]

Hi,

I have a table which contains a long string, and I need to query to get
three pieces of data from within the string as below:

DOC_CLASS=DL^DOC_NO=100102^DOC_REV=E^DOC_SHEET=1^

I need to search the string for the 100102 after DOC_NO, E after DOC_REV and
1 after DOC_SHEET.

Does anyone have any pointers on how to start?

Thanks,
Marc

 

[Marc T]

If it's any use whatsoever, here's the equivalent (probably inefficient!)
function that I used within an Excel worksheet to get Doc_No (where A1 = the
string):

=RIGHT(LEFT(A1,SEARCH("doc_rev",A1,1)-2),LEN(LEFT(A1,SEARCH("doc_rev",A1,1)-2))-SEARCH("doc_no",A1,1)-6)

Marc

 

[KARL DEWEY]

Assuming your document number is alway 6 digits --
DOC_NO: Mid([YourField], InStr([YourField], "DOC_NO=")+1), 6)
Assuming your Revision number not greater than 1 digit --
DOC_NO: Mid([YourField], InStr([YourField], "DOC_REV=")+1), 1)
Assuming your Sheet number not greater than 1 digit --
DOC_NO: Mid([YourField], InStr([YourField], "DOC_SHEET=")+1), 1)

If they may be greater then pull one more character and use 'Replace' to
remove '^' from end of shorter string.

 

[vanderghast]

Take a look at the VBA function Split to get each segment.

For each segment, spot the "=" sign with the function InStr and isolate the
of the segment which is right to it with the function Mid, but you probably
want to inspect what is to the left of the = to know which property the left
part of the = sign is.


If this step is a sub-step of a larger task: the XML reader could then be
the pre-packaged solution to your initial task, although I am not sure that
^ is a standard property delimiter for XML. You can even look for
possibilities to IMPORT the data to a table (with columns DOC_Class, DOC_no,
DOC_rev and DOC_sheet ), and then, get your data already nicely available,
in a standard Jet table,... look mom, no code!





Vanderghast, Access MVP

 

[Jerry Whittle]

Here's what I would do:

Make sure that the table has a primary key field. If not add an autonumber
field.

Export the data as a text file. I'd create a query that has the PK and the
field in question joined together like below and export it:

TheField: [PK] & "^" & [TheField]

Reimport the text field as its own table. When the Import Wizard gets to
Delimited or Fixed Width, chose Delimited. Next make the delimiter ^ by
clicking on the Other box and putting ^ in there. For some reason using
Access 2007, I had to bang on the ^ key a few times before it showed up.
Clicking out of the box seemed to help.

Next put in the field names that you want to see such as PK in Field 1,
DOC_CLASS in Field 2, and so on.

Import the data into it's own table.

Run update queries, or just plain old Find and Replace to get rid of
DOC_CLASS=, and so on for each field.

Join this new table with the existing table on the PK fields.

Now you can do nice, conventional queries on your data.

If you import this data frequently, you can set up procedures to do this in
the first place. Or, better yet, contact the people who give you this data in
the first place and have them export it in a better format. You'd be suprised
how often this works once you find the right people and ask, or bribe,
nicely.....

 

[Marc T]

Hi Karl,

thanks for the reply, definitely a point in the right direction for me!

Sadly the revision number and sheet number can both vary (not always by a
single digit either)

Marc

Assuming your document number is alway 6 digits --
DOC_NO: Mid([YourField], InStr([YourField], "DOC_NO=")+1), 6)
Assuming your Revision number not greater than 1 digit --
DOC_NO: Mid([YourField], InStr([YourField], "DOC_REV=")+1), 1)
Assuming your Sheet number not greater than 1 digit --
DOC_NO: Mid([YourField], InStr([YourField], "DOC_SHEET=")+1), 1)

If they may be greater then pull one more character and use 'Replace' to
remove '^' from end of shorter string.

--
Build a little, test a little.

Hi,

I have a table which contains a long string, and I need to query to get
three pieces of data from within the string as below:

DOC_CLASS=DL^DOC_NO=100102^DOC_REV=E^DOC_SHEET=1^

I need to search the string for the 100102 after DOC_NO, E after DOC_REV and
1 after DOC_SHEET.

Does anyone have any pointers on how to start?

Thanks,
Marc

 

[Marc T]

Hi Jerry,

thanks for the reply.

Problem is, the string is part of a field (KEY_REF) within a linked table
that I have no access to change. For whatever reason, our ERP system has the
table set up without these parts seperated.

Marc

Here's what I would do:

Make sure that the table has a primary key field. If not add an autonumber
field.

Export the data as a text file. I'd create a query that has the PK and the
field in question joined together like below and export it:

TheField: [PK] & "^" & [TheField]

Reimport the text field as its own table. When the Import Wizard gets to
Delimited or Fixed Width, chose Delimited. Next make the delimiter ^ by
clicking on the Other box and putting ^ in there. For some reason using
Access 2007, I had to bang on the ^ key a few times before it showed up.
Clicking out of the box seemed to help.

Next put in the field names that you want to see such as PK in Field 1,
DOC_CLASS in Field 2, and so on.

Import the data into it's own table.

Run update queries, or just plain old Find and Replace to get rid of
DOC_CLASS=, and so on for each field.

Join this new table with the existing table on the PK fields.

Now you can do nice, conventional queries on your data.

If you import this data frequently, you can set up procedures to do this in
the first place. Or, better yet, contact the people who give you this data in
the first place and have them export it in a better format. You'd be suprised
how often this works once you find the right people and ask, or bribe,
nicely.....

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

I have a table which contains a long string, and I need to query to get
three pieces of data from within the string as below:

DOC_CLASS=DL^DOC_NO=100102^DOC_REV=E^DOC_SHEET=1^

I need to search the string for the 100102 after DOC_NO, E after DOC_REV and
1 after DOC_SHEET.

Does anyone have any pointers on how to start?

Thanks,
Marc

 

[Dale Fye]

I'm with Michel (vanderghast) on this one. Use the Split function, or my
function fnParseText2( ).

To get the "100102" you would call this like:

fnparsetext2(fnparsetext2([Key_Ref], 2, "^"), 2, "=")

To get the "E" you would call this like:

fnparsetext2(fnparsetext2([Key_Ref], 3, "^"), 2, "=")

Here is the function.

Public Function fnParseText2(ByVal SomeValue As Variant, Position As
Integer, _
Optional ParseOn As String = " ") As Variant

Dim aArray() As String

'If the value passed was NULL, return a NULL
If IsNull(SomeValue) Then
fnParseText2 = Null
Exit Function
End If

SomeValue = Trim(SomeValue)

Position = Position - 1
aArray = Split(SomeValue, ParseOn)

If Position < LBound(aArray) Or Position > UBound(aArray) Then
fnParseText2 = Null
Else
fnParseText2 = aArray(Position)
End If

End Function