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

Editor to share with you the oracle sql language fuzzy query example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

Oracle in the Where clause, you can use the Like clause with wildcards to select those "very similar" for columns of field types such as datetime, char, and varchar. The following wildcards are available for data records

Zero or more characters

_ single any character (underscore)

\ special characters

There are four main functions that support regular expressions above oracle10g:

1REGEXPlayLIKE: the function is similar to LIKE.

2REGEXPlotINSTR: similar to the function of INSTR

3The function of SUBSTR is similar to that of REGEXPSTR.

4REGEXPLACE replace: similar to the function of REPLACE

POSIX regular expressions consist of standard metacharacters (metacharacters):

'^' matches the start of the input string, used in square brackets expressions, which indicates that the character collection is not accepted.

The'$'matches the end of the input string. If the Multiline property of the RegExp object is set, $also matches

Match'\ n' or'\ r'.

'.' Matches any single character except the newline character.

'?' Matches the previous subexpression zero or once.

The'+ 'matches the previous subexpression one or more times.

'*' matches the previous subexpression zero or more times.

'|' indicates a choice between two items. The example'^ ([aMusz] + | [0-9] +) $'represents the combination of all lowercase letters or numbers.

String.

'()' marks the start and end position of a subexpression.

'[]' marks a bracketed expression.

'{mdirection n}' an exact range of occurrences, m =

The second kind

Select * from user where username like'% ${value}%'

Note: ${} can receive simple type values or pojo attribute values. If parameterType transmits a single simple type value, ${} can only be value in parentheses. Using ${} can not prevent sql injection, but sometimes it is very convenient to use ${}

Treatment of dates

1. What is stored in the database is date using insert statements.

Insert into mytest_t values (1m 'Zhang San', to_date ('2011-09-09')) insert into mytest_t values (1 'Zhang San', to_date ('20110909'))

2. Varchar2 is inserted directly when it is stored in the database.

Insert into mytest_t values (1 recordable zhangdanzhangdanju 2011-09-09')

3, query, data inventory is varchar2

The format of the date output is determined by to_char

Select to_char (to_date (birthday01,'yyyy-mm-dd'), 'yyyy-MM-dd') from MYTEST_T output is: yyyy-MM-ddselect to_char (to_date (birthday01,'yyyy-mm-dd'),' yyyyMMdd') from MYTEST_T output is: yyyyMMdd

4, query, data inventory is date

The select to_char (birthday,'yyyy-MM-dd') from MYTEST_T format is the annual select to_char (sysdate,'mm') as myMonth from mytest of the time when the select to_char (birthday,'yyyyMMdd') from MYTEST_T select to_char (sysdate,'yyyy-MM-dd hh34:mi:ss') as nowTime from dual; / / date is converted into a string select to_char (sysdate,'yyyy') as myyear from mytest; / / determined by to_char. / / the monthly select to_char (sysdate,'dd') as myDay from mytest; / / the day select to_char (sysdate,'hh34') as myHour from mytest; / / the select to_char (sysdate,'mi') as myMinute from mytest; / / minutes select to_char (sysdate,'ss') as mySecond from mytest; / / seconds of the acquisition time

Add:

In the study of mybatis, when fuzzy query is used, it is found that an error will be reported when it is directly spliced into'%'{description}'%, and then three kinds of SQL sentences of fuzzy query are sorted out:

1. When passing parameters, concatenate the parameters as "%" + # {description} + "%" and then transfer them to the SQL statement in the XML file, thus avoiding splicing errors in the SQL statement (an "ORA-01722: invalid character" error may be reported in the ORACLE database, which can be modified to "%" for ORACLE | | # {description} | |% ").

two。 Use the concat function of SQL to concatenate SQL statements, so that the concatenated SQL statements also meet the requirements, such as:

SELECT * FROM MESSAGE WHERE DESCRIPTION like CONCAT (CONCAT ('%', # {description}),'%')

3. Instead of ${description}, # {description} can directly use'% ${description}%', such as:

SELECT * FROM MESSAGE WHERE DESCRIPTION like'% ${description}%'

Of the above three methods, the second is the most, and can be used in almost any situation, while the first and the third require some prerequisites before they can be used, but these three methods can be used in normal work. Sorting it out today will also provide a little help for you to encounter this situation again in the future.

Note: the third method is not recommended, there may be risk of sql injection.

The above is all the contents of the article "sample Analysis of Fuzzy query in oracle sql language". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.