CODEWITHSUNDEEP
sqlms-access
user2981940
user2981940

Reputation: 73

SQL - MS Access - search text between two known text string

I'm looking to extract 3 text part of below string.. 

Requested Ship Date: 05/31/2016 Please charge shipping to: ups 1234 PO Number: po 1234
  1. 05/31/2016
  2. ups 1234
  3. po 1234

Knowing that Requested Ship Date: and Please charge shipping to: and PO Number: never change.. but the character length of the second and third can change..

I'm not very good in SQL Access. I know that in Excel it would be easy with the right and find option.. but in Access?

Is there a way to find what's right of each : and then subtract the result?

Any ideas that can help me?

Upvotes: 2

Views: 679

Answers (1)

HansUp
HansUp

Reputation: 97101

Using Split() it would be easy to split the input string into pieces and extract the pieces you need. Here is an example from the Access Immediate window.

YourString = "Requested Ship Date: 05/31/2016 Please charge shipping to: ups 1234 PO Number: po 1234"
astrPieces = Split(YourString, " ")

? astrPieces(3)
05/31/2016
? astrPieces(9)
1234
? astrPieces(13)
1234

However, Split() can't be used directly in Access SQL. But, since your query will only be run within an Access session, you could create a VBA function which uses Split() and use that function in your query.

Here is an Immediate window example of such a function in action.

? GetShipData(YourString, "date")
05/31/2016
? GetShipData(YourString, "ups")
1234
? GetShipData(YourString, "po")
1234

So your query could be similar to this:

SELECT
    GetShipData([YourFieldName], "date") AS ship_date,
    GetShipData([YourFieldName], "ups") AS ups,
    GetShipData([YourFieldName], "po") AS po
FROM YourTable;

Here is the function I tested in the Immediate window example above:

Public Function GetShipData(ByVal pInput As String, ByVal pWhichItem As String) As String
    Dim astrPieces() As String
    Dim strReturn As String

    astrPieces = Split(pInput, " ")
    Select Case pWhichItem
    Case "date"
        strReturn = astrPieces(3)
    Case "ups"
        strReturn = astrPieces(9)
    Case "po"
        strReturn = astrPieces(13)
    Case Else
        strReturn = "fail" '<- figure out what you want here
    End Select
    GetShipData = strReturn
End Function

You will probably want to refine that function in case any of your input strings don't match the pattern of your question's sample string.

Upvotes: 2

Related Questions