none
How to match text regardless of case

    Question

  • This seems like there should be an easy answer to this, but I've been searching the net and cannot find a solution.

    In SQL 2008 Reporting Services, I have the following expression:

    =Iif((Fields!Country.Value = "mycountry" or Fields!Country.Value = "country"), Nothing, Fields!Country.Value)

    How do I make this expression case insensitive? I want to match "country", "mycountry", "MyCountry", "COUNTRY", etc. Is there a function such as the following to do this?

    =Iif(IgnoreCase(Fields! Country.Value =....

    Or is there a regular expression that can be used for the purpose?

    Thanks!

    Friday, November 22, 2013 8:11 PM

Answers

  • Hi, Use the below expression.

    =Iif((LCase(Fields!Country.Value) = "mycountry" or LCase(Fields!Country.Value) = "country"), Nothing, Fields!Country.Value)

    Hope this helps...........


    Ione

    Friday, November 22, 2013 8:24 PM
  • You basically eliminate the problem by making everything lower case before comparing.  I think Ione solution is the good one.

    just make sure you put both the value on the left and the value on the right of your comparison in lower case.

    lcase("MyCountry") = lcase("MYCOUNTRY") = lcase("mycountry") = "mycountry" and so forth.

    Friday, November 22, 2013 10:07 PM
  • Hi johnkuehne,

    Just as you know, LCase function converts a string to lower case. If you directly use the function as =lcase("MyCountry"), it will return “mycountry”. So the expression posted by Ione contains three steps:

    1. Convert the value of Country field (including "mycountry", "Mycountry", "MyCountry", "MYCOUNTRY", etc.) to lower case (“mycountry”).
    2. Determine whether the return value is "mycountry".
    3. If it returns “mycountry”, output Nothing; if not, output the original value of Country field.

    To sum up, I think Ione solution can better meet your requirement. As to Antoine suggestion, he want to tell you that you can try to use those expressions (including =lcase("MyCountry"), = lcase("MYCOUNTRY"), = lcase("mycountry")) to see if you can get the “mycountry” as returns. If it works well in your environment, I strongly suggest that you can double-check the expression posted by Ione to see whether you have type it correctly. I still think this expression is a good solution.

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, November 25, 2013 2:34 AM
    Moderator

All replies

  • Hi, Use the below expression.

    =Iif((LCase(Fields!Country.Value) = "mycountry" or LCase(Fields!Country.Value) = "country"), Nothing, Fields!Country.Value)

    Hope this helps...........


    Ione

    Friday, November 22, 2013 8:24 PM
  • Thanks for the reply, Ione, but I'm confused. I thought LCase converts a string to lowercase, so that if "Mycountry" was input, it would return "mycountry". I want the expression to ignore case altogether so that if "mycountry", "Mycountry", "MyCountry", or "MYCOUNTRY"is input, the form will evaluate them as the same value and, in my example above, output Nothing.

    I just tried using LCase in my form expression and it did not work.

    Any other suggestions would be welcome.

    • Proposed as answer by Antoine F Friday, November 22, 2013 10:01 PM
    • Unproposed as answer by Antoine F Friday, November 22, 2013 10:08 PM
    Friday, November 22, 2013 9:04 PM
  • You basically eliminate the problem by making everything lower case before comparing.  I think Ione solution is the good one.

    just make sure you put both the value on the left and the value on the right of your comparison in lower case.

    lcase("MyCountry") = lcase("MYCOUNTRY") = lcase("mycountry") = "mycountry" and so forth.

    Friday, November 22, 2013 10:07 PM
  • Unfortunately, Antoine, that's not possible. The data is coming from a pre-configured database where multiple users are inputting the data, and there is no way to ensure that the data is always entered with the same formatting.

    I Know that this function exists in other languages as illustrated in the example in my original question. Are you suggesting that there is no way to do what I want in Reporting Services?

    • Edited by johnkuehne Friday, November 22, 2013 11:07 PM
    Friday, November 22, 2013 11:05 PM
  • Hi johnkuehne,

    Just as you know, LCase function converts a string to lower case. If you directly use the function as =lcase("MyCountry"), it will return “mycountry”. So the expression posted by Ione contains three steps:

    1. Convert the value of Country field (including "mycountry", "Mycountry", "MyCountry", "MYCOUNTRY", etc.) to lower case (“mycountry”).
    2. Determine whether the return value is "mycountry".
    3. If it returns “mycountry”, output Nothing; if not, output the original value of Country field.

    To sum up, I think Ione solution can better meet your requirement. As to Antoine suggestion, he want to tell you that you can try to use those expressions (including =lcase("MyCountry"), = lcase("MYCOUNTRY"), = lcase("mycountry")) to see if you can get the “mycountry” as returns. If it works well in your environment, I strongly suggest that you can double-check the expression posted by Ione to see whether you have type it correctly. I still think this expression is a good solution.

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, November 25, 2013 2:34 AM
    Moderator
  • Katherine,

    Thank you for clarifying how the function works. I did not understand the exact mechanism involved and must have made a syntax error when I tested Ione's solution. It is working now. Thanks to everyone for your help.

    Monday, November 25, 2013 2:39 PM