Need help with CONTAINS function

[rhyatt]

I'm trying to see if customer search terms match a product name that was later clicked on. I'm using the contains function to see if the product name contains what the customer used as a search term.  For the most part it's working, but one weird thing I keep seeing is that if there is a space then the function isn't counting it as a match. 

For example: 

search #	search term	product name	match
1	nike	nike air	yes
2	nike air	nike air	no

On search # 1 the product name 'nike air' contains the search term 'nike', so for my purposes that is a match. 

The problem is that on search #2 even though the search term and product name are exact, it's still saying that it isn't a match. I thought maybe case was an issue, so i accounted for that (the product name is always uppercase so I made the search term uppercase).  I also thought maybe leading trailing spaces were an issue, so I accounted for that (with trim).  But it's still not working.

I'm guessing there's a flaw in my code?

IF [TradeName] != NULL()
AND [searchterm] != ''
AND CONTAINS(TRIM([TradeName]),Uppercase(TRIM([searchterm])))
THEN 'Yes'
ELSEIF [TradeName] != NULL()
AND [searchterm] != ''
AND NOT CONTAINS(TRIM([TradeName]),Uppercase(TRIM([searchterm])))
THEN 'No'
ELSE Null()
ENDIF

[rhyatt]

I'm trying to see if customer search terms match a product name that was later clicked on. I'm using the contains function to see if the product name contains what the customer used as a search term.  For the most part it's working, but one weird thing I keep seeing is that if there is a space then the function isn't counting it as a match. 

For example: 

search #	search term	product name	match
1	nike	nike air	yes
2	nike air	nike air	no

On search # 1 the product name 'nike air' contains the search term 'nike', so for my purposes that is a match. 

The problem is that on search #2 even though the search term and product name are exact, it's still saying that it isn't a match. I thought maybe case was an issue, so i accounted for that (the product name is always uppercase so I made the search term uppercase).  I also thought maybe leading trailing spaces were an issue, so I accounted for that (with trim).  But it's still not working.

I'm guessing there's a flaw in my code?

IF [TradeName] != NULL()
AND [searchterm] != ''
AND CONTAINS(TRIM([TradeName]),Uppercase(TRIM([searchterm])))
THEN 'Yes'
ELSEIF [TradeName] != NULL()
AND [searchterm] != ''
AND NOT CONTAINS(TRIM([TradeName]),Uppercase(TRIM([searchterm])))
THEN 'No'
ELSE Null()
ENDIF

[JohnJPS]

HI @rhyatt,

I'm just curious, in the example given, what if you were to do UPPERCASE on both the lookup term and the product term?

[mborriero]

I tried to re-create the issue but it works fine... try it ( I have add @JohnJPS suggestion but it did work before).

Are you sure that the strings you are comparing do not have any other special character other than space (tab?)?

when you check the data, try to tick the highlighted symbol to see if you have any "weird" chars in your data

[rhyatt]

Just tried that, unfortunately the same result.  It still doesn't want to work with that space in there.

[rhyatt]

Hmmm I don't have that symbol as an option in my results?

[mborriero · Accepted solution]

double click on one field (string) it will appear hopefully

[rhyatt · Accepted solution]

Ok thanks, got the tool...and I see what you're talking about.

So for this example,it says the terms don't match.

the search term shows as: lithium carbonate

and the trade name shows a dot character in-between the words: lithium *carbonate (it actually shows a dot, but i just used an asterisk for my visual example).

So you've helped me identify the issue! Now I'm just not sure what to do to fix it...??

EDIT**

I think I found the solution.  I used the Data Cleansing tool, and under the section of REMOVE UNWANTED CHARACTERS I checked the box for 'tabs, linebreaks, and duplicate whitespace'.  So far it looks like it's working....

[mborriero]

Sorry for not coming back to you, I have missed your answer.

Glad to hear you solved it.