8 Replies Latest reply: Jul 31, 2017 8:05 AM by Mike Slottje RSS

    Searching text string within field in table

    Mike Slottje

      Hi experts,

       

      I'm facing the following problem and would like to ask you experts for help.

       

      In a table I have a document number that contains 4 fixed characters (red) and 5 random characters (blue), i.e. IB17-0123.

      Let's call this table the IB table.

       

      Next to the IB table, I have a quite incomplete table which I want to left join with the IB table.

      This incomplete table doesn't have a unique document number that could be used as a key field to match with the IB table.

      My only option is a description field where some details as name, date and IB are mentioned (manually).

      Unfortunately the order of these details are not always the same.

      So, "Name, IB number, date" or "IB number, Name, date" or "date, Name, IB number" etc.

       

      I'm looking for a function which searches for the complete IB number in the description and excludes the other characters (details) in this field.

      In this way, I want to create a field that could be used as a key field for the left join in my loading script.

      How should such a function look a like?

       

      Please note that I'm aware of the fact this is far from ideal and that a solution in our system/database would be much safer and easier.

      Let me know if I should clarify something:)

       

      Cheers,

      Mike