In the great god, I want to check the name does not contain the letter o, according to the understanding I think that the effect of the following statements are equivalent, but add the not can find out the result, and used to exclude the wildcard but did not find any record, also is not an error, also tried to '% [! O] %' form, is still not as a result, the database is Oracle, is the reason why is because the database does not support??
For the great god grant instruction

 select * from employees_copy t where t.f irst_name not like '% % o'; 
Select * from employees_copy t where t.f irst_name like '%] [^ o %'; 

CodePudding user response:

Like does not support a regular expression, change regexp_like

CodePudding user response:

 
-- query name contains letters o 
Select * from employees_copy where regexp_like (first_name, '[o] +'); 
-- does not include the letters in the query name o 
Select * from employees_copy where not regexp_like (first_name, '[o] +'); 

CodePudding user response:

 
With TMP as 
(
Select 'aboc first_name from dual union all 
Select the 'ABC' first_name from dual 
) 
Select * 
The from TMP 
Where regexp_like (first_name, '[o] +'); 

With TMP as 
(
Select 'aboc first_name from dual union all 
Select the 'ABC' first_name from dual 
) 
Select * 
The from TMP 
Where not regexp_like (first_name, '[o] +'); 

I just test on the computer, can satisfy the demand of you,

CodePudding user response:

Just checked the, it seems that someone else had the same problem, to see someone to check with regular expressions, tried, the following statement can be found to correct the results

 select * from employees_copy t where regexp_like (t.f irst_name, '^ [m] - o'); - to m, n or o start 
Select * from employees_copy t where regexp_like (t.f irst_name, '^] [^ m - o'); - not to m, n or o start 
Select * from employees_copy t where regexp_like (t.f irst_name, '[m] - o $'); End - to m, n or o 
Select * from employees_copy t where regexp_like (t.f irst_name, '[^ m - o] $'); - not to m, n or o end 

The information on the expression of two symbols to is explained:
POSIX regular expression by standard metacharacters (metacharacters) :
'^' matches the starting position of the input string, in square brackets expressions used, meanwhile it said not to accept the character set,
'$' at the end of the input string matching position, set up a RegExp Multiline attribute of an object, if is $also match' \ n 'or' \ r ',

According to the test, my understanding is' ^ 'within [] said is negative, it indicates that the need to the results of the query does not contain these characters in brackets, if on the front of the [] the beginning, that is the results of the query to begin with these characters in brackets,' $' if placed in [] and the other letters are said, if the end, on the back of the [] say that the results of the query to ends with these characters in the brackets, but not both at the same time in the beginning and end, like this:

 select * from employees_copy t where regexp_like (t.f irst_name, '^ [m] - o $'); 

Now there are two problems: the understanding of the above correct? In Oracle to write with [] said the scope of the fuzzy query whether can use regular expressions or give up the use of the brackets with the not directly like? The great god, please grant instruction

CodePudding user response:

refer to the second floor qq646748739 response:

 
-- query name contains letters o 
Select * from employees_copy where regexp_like (first_name, '[o] +'); 
-- does not include the letters in the query name o 
Select * from employees_copy where not regexp_like (first_name, '[o] +'); 

Main layer of this kind of writing is more concise, than I want to ask '[o] +' behind this kind of writing is to follow the rules of what, can say in detail

CodePudding user response:

reference 4 floor sinat_35705122 response:

just checked the, seems to be someone else had the same problem, to see someone to check with regular expressions, tried, the following statement can be found to correct the results

 select * from employees_copy t where regexp_like (t.f irst_name, '^ [m] - o'); - to m, n or o start 
Select * from employees_copy t where regexp_like (t.f irst_name, '^] [^ m - o'); - not to m, n or o start 
Select * from employees_copy t where regexp_like (t.f irst_name, '[m] - o $'); End - to m, n or o 
Select * from employees_copy t where regexp_like (t.f irst_name, '[^ m - o] $'); - not to m, n or o end 

The information on the expression of two symbols to is explained:
POSIX regular expression by standard metacharacters (metacharacters) :
'^' matches the starting position of the input string, in square brackets expressions used, meanwhile it said not to accept the character set,
'$' at the end of the input string matching position, set up a RegExp Multiline attribute of an object, if is $also match' \ n 'or' \ r ',

According to the test, my understanding is' ^ 'within [] said is negative, it indicates that the need to the results of the query does not contain these characters in brackets, if on the front of the [] the beginning, that is the results of the query to begin with these characters in brackets,' $' if placed in [] and the other letters are said, if the end, on the back of the [] say that the results of the query to ends with these characters in the brackets, but not both at the same time in the beginning and end, like this:

 select * from employees_copy t where regexp_like (t.f irst_name, '^ [m] - o $'); 

Now there are two problems: the understanding of the above correct? In Oracle to write with [] said the scope of the fuzzy query whether can use regular expressions or give up the use of the brackets with the not directly like? The great god, please grant instruction!

Your understanding is right for the most part, but:
1. ^ and $cannot simultaneously on both the beginning and end, this is wrong, respectively in the beginning and end is very normal,
For example: I query begin with "yue", 8 at the end of the license plate, will be used at the same time ^ and $
2. About the second point: the Oracle to write with [] said the scope of the fuzzy query whether can use regular expressions or give up the use of the brackets with the not directly like
This is right, with [] can only use the normal expression, otherwise use like or not like,

CodePudding user response:

reference 4 floor sinat_35705122 response:

just checked the, seems to be someone else had the same problem, to see someone to check with regular expressions, tried, the following statement can be found to correct the results

 select * from employees_copy t where regexp_like (t.f irst_name, '^ [m] - o'); - to m, n or o start 
Select * from employees_copy t where regexp_like (t.f irst_name, '^] [^ m - o'); - not to m, n or o start 
Select * from employees_copy t where regexp_like (t.f irst_name, '[m] - o $'); End - to m, n or o 
Select * from employees_copy t where regexp_like (t.f irst_name, '[^ m - o] $'); - not to m, n or o end 

You check the regular expression is the statistics of the beginning and end, also do not recommend this to write,
In addition with your demand: they contain, there is a difference between, such as o among first_name, will not be able to query,

CodePudding user response:

reference 5 floor sinat_35705122 reply:

Quote: refer to the second floor qq646748739 response:

 
-- query name contains letters o 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull