Search function to ignore uppercase lowercase and "-"

[emiliocg]

We created a looker studio dashboard and we need to enhance our search function.

1. How can we design the search function to handle variations in the part number format, including upper/lowercase and special characters like "-"?
2. Is it possible to create a solution that can handle different SKU/part numbers syntax?
3. What approach should be taken to ensure that incomplete part numbers can still be effectively searched for?

So, here are examples of what we need:

P1111208 needs to found by P111-1208  or     p111-1208  or P1111208
F44485D1   needs to be found by F44485D1    or   F44485D1     f44485d1
T117788-01   needs to be found by   t117788-01  or  t11778801   or  T11778801

I think this would solve the Upper / Lowercase, but not sure about the "-" dash character 
https://support.google.com/looker-studio/thread/79360093/i-want-to-remove-the-match-type-characters-...

Any guidance will be appreciated.

[sam8]

I'd recommend using LOWER (or UPPER) in your search to make sure all capitalization is consistent. 

As for regexp replace, yeah you're on the right track! You can modify that example to include a dash like so:
REGEXP_REPLACE(Field, "([\\+\-\\[\\]])", "")
 

[emiliocg]

Thanks Sam8!

[emiliocg · Accepted solution]

Here is the way I ended up solving the issue.

1. Converting everything to lower case, using LOWER and eliminating "-", it seems like REPLACE used as below is working as expected
LOWER(REPLACE(PartNumber, "-", ""))

I did that for the Part Number and the Search Text

1. Handle Variations in Part Number Format

To ensure the search function can handle variations in part number format (including uppercase, lowercase, and special characters like dashes), you can use a combination of data transformations and search logic.

Steps:

1. Normalize Data:

   • Convert to Lowercase: Ensure all part numbers in your dataset are converted to lowercase to handle case insensitivity.
   • Remove Special Characters: Strip out special characters like dashes from the part numbers in your dataset and from the search input.

2. Create Calculated Fields:

   • Create a calculated field in Looker Studio that normalizes the part numbers by converting them to lowercase and removing special characters.

Example Calculated Fields

Normalized Part Number

LOWER(REPLACE(PartNumber, "-", ""))

Normalized Search Input

In your search input processing, ensure the input is normalized similarly by converting it to lowercase and removing special characters.

2. Handle Different SKU/Part Number Syntax

By normalizing the part numbers and the search input, you can ensure that variations in syntax are handled effectively.

3. Ensure Incomplete Part Numbers Can Be Effectively Searched

To handle incomplete part numbers, you can use a CONTAINS or LIKE clause in your search logic.

Example SQL Query

Assuming you have a normalized part number field (NormalizedPartNumber) and a normalized search input (normalized_search_input), your query might look like this:

SELECT * FROM YourTable WHERE LOWER(REPLACE(PartNumber, "-", "")) LIKE CONCAT('%', normalized_search_input, '%')

This query will allow you to search for part numbers even if the input is incomplete.

Implementing in Looker Studio

1. Create a Parameter for Search Input:

   • Create a parameter to capture the user's search input.

2. Create a Filter:

   • Use a filter in Looker Studio to apply the search logic. The filter should use the normalized part number and the normalized search input.

Example Implementation

1. Create a Calculated Field for Normalized Part Number:

   • Go to your data source in Looker Studio.
   • Add a new calculated field:
     NormalizedPartNumber = LOWER(REPLACE(PartNumber, "-", ""))

2. Create a Parameter for Search Input:

   • Create a parameter called SearchInput and set its type to text.

3. Create a Calculated Field for Normalized Search Input:

   NormalizedSearchInput = LOWER(REPLACE(SearchInput, "-", ""))

4. Create a Filter:

   • Create a filter that uses the normalized part number and the normalized search input.
     NormalizedPartNumber CONTAINS NormalizedSearchInput