Difference Between '%In%' and '=='

Difference between the == and %in% operators in R

%in% is value matching and "returns a vector of the positions of (first) matches of its first argument in its second" (See help('%in%')) This means you could compare vectors of different lengths to see if elements of one vector match at least one element in another. The length of output will be equal to the length of the vector being compared (the first one).

1:2 %in% rep(1:2,5)
#[1] TRUE TRUE

rep(1:2,5) %in% 1:2
#[1] TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE

#Note this output is longer in second

== is logical operator meant to compare if two things are exactly equal. If the vectors are of equal length, elements will be compared element-wise. If not, vectors will be recycled. The length of output will be equal to the length of the longer vector.

1:2 == rep(1:2,5)
#[1] TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE

rep(1:2,5) == 1:2
#[1] TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE

1:10 %in% 3:7
#[1] FALSE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE

#is same as 

sapply(1:10, function(a) any(a == 3:7))
#[1] FALSE FALSE  TRUE  TRUE  TRUE  TRUE  TRUE FALSE FALSE FALSE

NOTE: If possible, try to use identical or all.equal instead of == and.

Difference between _%_% and __% in sql server

Both start with A, and end with %. In the middle part, the first says "one char, then between zero and many chars, then one char", while the second one says "one char, then one char".

Considering that the part that comes after them (the final part) is %, which means "between zero and many chars", I can only see both clauses as identical, as they both essentially just want a string starting with A then at least two following characters. Perhaps if there were at least some limitations on what characters were allowed by the _, then maybe they could have been different.

If I had to choose, I'd go with the second one for being more intuitive. After all, many other masks (e.g. a%%%%%%_%%_%%%%%) will yield the same effect, but why the weird complexity?

What is the difference between LIKE '[A-D]%' ; and BETWEEN 'A' AND 'D' ; in SQL Server

Let me assume you are talking about SQL Server, not MySQL.

SQL Server has limited extensions of LIKE that support character ranges. So, you are comparing:

where name like '[A-D]%' 
where name between 'A' and 'D'

The difference is simple. A name longer than "D" that starts with "D" matches the first condition but not the second. For instance, 'Dionysis' and 'Dracula' match the like but not the between.

The equivalent comparison would be:

where name >= 'A' and name < 'E'

What's the difference between LIKE and = in SQL?

As per SQL standard, the difference is treatment of trailing whitespace in CHAR columns. Example:

create table t1 ( c10 char(10) );
insert into t1 values ('davyjones');

select * from t1 where c10 = 'davyjones';
-- yields 1 row

select * from t1 where c10 like 'davyjones';
-- yields 0 rows

Of course, assuming you run this on a standard-compliant DBMS. BTW, this is one the main differences between CHARs and VARCHARs.

sql: what is the difference between LIKE %...% and LIKE?

In your First query it shows all config column data which contains code = LIST
In your second query it shows all config column data which exact to code = LIST

LIKE supports wildcards. Usually it uses the % or _ character for the wildcard.

For know more about LIKE

Difference between LIKE and ~ in Postgres

~ is the regular expression operator, and has the capabilities implied by that. You can specify a full range of regular expression wildcards and quantifiers; see the documentation for details. It is certainly more powerful than LIKE, and should be used when that power is needed, but they serve different purposes.

Is there a combination of LIKE and IN in SQL?

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

The column you are querying must be full-text indexed.

Reference:

Building Full-Text Search Applications with Oracle Text
Understanding SQL Server Full-Text

What is the difference between the following sql queries?

The difference is only that in second one you have a filter that is not filtering anything.

The LIKE operator works by searching a text or part of a text from a field. The % is a wildcard that is used to replace a set of characters (any amount).

In your case the second query is returning all rows because your given filter column1 like '%%' is evaluating to everything.

It works like this.

Imagine that your field column1 has these values

foo
bar
goo
boo
zar

So if you use column1 like '%%' you are saying to your database: give me all rows that the text into that column1 starts with anything (any times) and ends with anything. So everything. It will also return all rows if you use column1 like '%'

But if you use column1 like '%oo%' it will give you as answer foo, goo and boo or if you use column1 like '%r' you will have bar and zar

Hope it helps you to understand.