The SEARCH function returns the position (counting from the left) of the first occurrence of a character or of a text string within another text string, and it can be specified from which character the search begins. This function is accent-sensitive but case-insensitive.
If you want to perform a case-sensitive (and accent-sensitive) search, you should use the FIND function.
SEARCH(
find_text,
within_text
[, [start_num]
[, NotFoundValue]]
)
- find_text: Character or character string searched or name of the column from which the text string to be found will be extracted. It is possible to use the wildcards "?" (representing a single character) and "*" (representing an indefinite number of characters, including the empty string). If you want to search for a question mark or an asterisk, it must be preceded by the character "~".
- within_text: Text string in which the search of find_text will be carried out or name of the column in which the search will be carried out.
- start_num: Optional argument. Starting position in within_text (counting from the left) from which the search will be carried out, ignoring the previous characters. If it is omitted, it will be considered as containing the value 1 (that is, the search will be carried out from the first character).
- NotFoundValue: Optional argument. Numeric value to return when the text string is not found, a value that will normally be -1 or 0 (values that can never be confused with those returned by the function when the searched string is found) or BLANK (empty value).
The SEARCH function returns an integer representing the position of the searched text to be counted from the left (the first character occupies position 1) after ignoring the content of the characters indicated by the optional start_num argument.
The SEARCH function is not case sensitive. In this way, the following two formulas:
= SEARCH("n", columnName)
= SEARCH("N" , columnName )
...will return the same results.
However, accented characters are considered different characters. In this way, all of the following formulas will return different results:
= SEARCH("a", columnName)
= SEARCH("á" , columnName )
= SEARCH("à" , columnName )
(Power BI is converting the "A" in the second row and the "N" in the eleventh row to lowercase due to some unknown error)
It can be seen that "ñ" is also considered a different letter than "n".
Unless the fourth argument (NotFoundValue) is included, if the searched text is not found, SEARCH returns an error (and this will be displayed in all rows even if only some do not contain the searched text string).
This DAX function can return different results when used in DirectQuery mode.
The following example uses the SEARCH function to find the first occurrence of the letter "a" in the "Manufacturer" column that contains manufacturer names, and the MID function to return three characters from that position. The MID function is included as the first argument of the IFERROR function so that, in those cases in which no letter "a" is found (occasions when the SEARCH function is going to return an error), a controlled result is returned (a Blank in this case).
Note that, in the last row, the letter "a" occupies the last position of the text string, so MID only returns that letter (which can be confirmed by looking at the length of the extracted text).
The following example uses the SEARCH function to find the first occurrence of the letter "a" in the "Manufacturer" column, and the REPLACE function to replace two characters from that position with "__". Again, the IFERROR function is used to return a controlled result in case of error.
Note how, in the last row, the letter "a" occupies the last position of the text string, so when replacing it with "__", the length of the string returned by REPLACE increases by one.
