Oracle database, ignoring case like fuzzy query (SQL server,mysql principle Same)

Background

When using Oracle or other databases, using the LIKE keyword for fuzzy query is commonly used by people, in the use of pure Chinese environment is very easy to use, there are some wildcards can be used, but in a pure English environment, the size of the need to accurately match the problem, the main reason is the problem of string

FL like '%{0}% ' and

Here like after is a string, so there must be a size-sensitive problem. For example, the following fields of mixed size

Solution Solutions

Scenario 1

Use the Oracle system functions to lowercase convert the column string that needs to be queried (uppercase is also the case, the relevant parts of the variable are uppercase conversions), as follows:

SELECT * from logo where lower (bsname) like '%cz% '

Of course, that's not enough. You need to convert the variables in your code to the appropriate lowercase

String.Format ("SELECT * from logo where lower (bsname) like '%{0}% '", TBFL. ToLower ());

Advantages of this scheme:

Database compatibility is good, both SQL Server and MySQL can be implemented according to the corresponding principle

Disadvantages:

Add extra function code in SQL and code that doesn't look so clean

Scenario 2

Use the Oracle Regular expression syntax to complete case-insensitive fuzzy matches, as follows

String.Format ("Regexp_like (Bsname, ' [: graph:]*{0}[:graph:]* ', ' I ') and", Tbbs);

There may be people here who feel that writing is too shallow, may feel "regexp_like", "[: graph:]*" is what ghost?

In fact, with my pragmatic point of view is fully sufficient to achieve a like fuzzy query case matching is enough.

Read the relevant data, found that Oracle's regular expression corresponds to Java (and C # naming some differences), here

Regexp_like is a function of Oracle regular matching

[: graph:]* is matched with any string (0 or n characters)

The "I" parameter is the meaning of ignoring the string size

Specifically, if you want to learn about the use of Oracle regular expressions, I'll attach some links. Here is also the place where I want to vomit, probably also as a pragmatist some ideas, when I do this demand, Baidu to basically is program 2, but some of the data is basically the Oracle regular expressions listed to explain it again, In fact, I want to be a regular simulation like the function, I think if as a not proficient in the regular novice, learning a lot may not be able to use, so I have to share the idea of this article, if there is need to take to use good, if the need for regular, then further study.

Advantages of this scheme:

Simple to use, clean statement, less place to consider

Disadvantages:

Database is incompatible (other databases may have different functions and need to consult the data)

Written in the last

This article is very short, but it is my original, but also some small experience in the work, I hope that readers you are through the search engine to find it, but also hope it can help your work, and this is my true meaning of the first original blog post, I also try to add a reward function, after all, I still dick Silk, Of course, the spirit of encouragement is the same, I hope to get your encouragement. BTW, hope it is useful!

Disclaimer

The article provides the concrete idea and the realization way, but because does not carry on the strict code test, does not guarantee the execution 100% correct, if has the question also may feedback to oneself or the message and I discuss, common improvement, common progress.

Related information

SQL like wildcard characters

An explanation of the use of the Oracle regular expression regexp_like

Oracle database, ignoring case like fuzzy query (SQL server,mysql principle Same)