How to perform exact match on concatenated string

[#1]

Hello,

I have a scenario like the following

Table:

TESTING123

TESTING

Concatenated String:

TESTING123|TESTING

When I do a match in table for TESTING, I'm getting 2 items returned.  How can I make it so that my match ensures that the whole string matches and not something like a "contains"?  I can't really use match() because I'm actually looking in a concatenated string.  Also, I'll never know at what position my string exists.  Currently, I'm doing an Index(), but that's returning both strings, not just the 1 that I want.  Hope that makes sense.  Thanks!

[Clever_Anjos]

Are you using match function? a listbox?

[Anonymous]

Looks familiar.  In situations like this, I avoid values that can be substrings of the other values.  Replace 'TESTING' with something like 'TESTING_'

[#4]

I wish I could, but I can't. These values are coming from a database and we can't change the database beacuse of the impact that would have.

Sent from my mobile device

[Anonymous]

Sorry about that.

Still, there are always solutions.  In this particular case:

LOAD

...

if(Field='TESTING', 'TESTING_', Field) as Field

;

SELECT...

Or maybe add a character at the end of each value, it is more reliable, reduces chances to have values which are substrings of the other values:

Field & '_' as Field

Regards,

Michael

[tresesco]

Though not very clear about the exact requirement, you might want to have a look at FindOneOf() function which gives you the position of the matched string for n'th occurance.

[#7]

But then won't my listboxes have the additional character as well for those selections? That's not an acceptable solution if so.

Sent from my mobile device

[Anonymous]

This can be done if you keep both fields, the original for the list boxes, and a copy for concatenation:

LOAD

...

Field,

Field & '_' as Field2

...

(I know it is also not perfect)

[Clever_Anjos · Accepted solution]

Maybe a little trick can do what you want

=index(Concat(F1&'|'),'Testing'&'|')  only return 12

Table:
LOAD * INLINE [
    F1
    Testing
    Test
    Testing123
];

[Anonymous]

Instead of adding "suffix" in the script, you're adding it right there in expression.

Looks reasonable.