Shulou(Shulou.com)05/31 Report--

How to match multiple fields with a keyword in Oracle? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

Analysis:

In general, one of our input boxes corresponds to the search for a column of information in the database, for example, if the first name begins with the surname'Li', then the corresponding sql is

Where name like'Li%'

However, now there is uncertainty in the input box, and we don't know what the user will type, so that our background sql doesn't know how to correspond.

For example, if the user enters 18, it is obvious that the user wants to search for users at the age of 18. If the background sql is still where name like'18% search, then the content will not be found.

Option 1:

We can use the keyword or to achieve multi-field matching

For example: where name like'% search content%'or age like'% search content%'

This scheme can be tried for those with few search fields, but it is not efficient.

Option 2: recommend

We can think differently.

Suppose our database has a complete column (colum) for each row, and the contents of this column are the contents of all the fields in that row.

Then the sql we searched can be rewritten as where colum like'% search content%'.

In this way, we can find out whether we search for name, age, address, etc.

Now that you're here, I'm sure you already know what to do.

In fact, it is to use sql to stitch together the database fields that need to be searched by users, and then like'% search content%'.

Oracle practice

You can use the instr () function. Here we talk about the function of the INSTR () function.

INSTR (parameter 1, parameter 2) is actually a function to find a string, which returns the position of the string lookup. It has two parameters of character type. If found, it returns the position of parameter 2 in parameter 1, and returns 0 if it is not found.

SELECT INSTR ('abcde', 'a') FROM DUAL

-

one

-

Result analysis, because an is in the first position of abcde, it returns 1

SELECT INSTR ('abcde',' f') FROM DUAL

-

0

-

Result analysis, 0 is returned because f cannot be found in the abcde string

Compare like with the instr () function. If you have strict performance requirements for children's shoes, remember to replace the like keyword with instr () here.

Colum like'% search content%'is equivalent to instr (colum, 'search content', 1, 1) > 0colum like 'search content%' is equivalent to instr (colum, 'search content', 1, 1) = 1colum like'% search content 'equivalent to instr (colum,' search content', 1,-1) = length (colum)-length ('search content') + 1

Sql:

Oracle | | indicates splicing multiple database columns

General situation: where instr (name | | Age | | address, 'search content', 1) > 0

If two data columns are adjacent, split by','to prevent mismatches from being found:

Where instr (name | |','| | Age | |','| | address, 'search content', 1) > 0

If the field is empty, you can use nvl (field name,''), which means to replace the empty content with''.

Mysql

Concat splices multiple database columns

Instr query

Where instr (concat (name, age, address), 'search content') > 0

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.