In SQL, we can compare two strings using STRCMP () function. STRCMP () returns '0' when the two strings are the same, returns '-1' if the first string is smaller than the second string, and returns 1 if the first string is larger than the second string.
MySQL STRCMP() FunctionThe STRCMP() function compares two strings.
The right way of comparing String in Java is to either use equals(), equalsIgnoreCase(), or compareTo() method. You should use equals() method to check if two String contains exactly same characters in same order. It returns true if two String are equal or false if unequal.
You may come across the situation, where you need alternate to “like” keyword of SQL, that is to search for sub-string in columns of the table. The one way to achieve it to use instr() function, instr() function takes 3 parameters in account .
LIKE
and the equality operator have different purposes, they don't do the same thing:=
is much faster, whereas LIKE
can interpret wildcards. Use =
wherever you can and LIKE
wherever you must.
SELECT * FROM user WHERE login LIKE 'Test%';
Sample matches:
TestUser1
TestUser2
TestU
Test
To see the performance difference, try this:
SELECT count(*)
FROM master..sysobjects as A
JOIN tempdb..sysobjects as B
on A.name = B.name
SELECT count(*)
FROM master..sysobjects as A
JOIN tempdb..sysobjects as B
on A.name LIKE B.name
Comparing strings with '=' is much faster.
In my small experience:
"=" for Exact Matches.
"LIKE" for Partial Matches.
There's a couple of other tricks that Postgres offers for string matching (if that happens to be your DB):
ILIKE, which is a case insensitive LIKE match:
select * from people where name ilike 'JOHN'
Matches:
And if you want to get really mad you can use regular expressions:
select * from people where name ~ 'John.*'
Matches:
Just as a heads up, the '=' operator will pad strings with spaces in Transact-SQL. So 'abc' = 'abc '
will return true; 'abc' LIKE 'abc '
will return false. In most cases '=' will be correct, but in a recent case of mine it was not.
So while '=' is faster, LIKE might more explicitly state your intentions.
http://support.microsoft.com/kb/316626
For pattern matching use LIKE. For exact match =.
LIKE
is used for pattern matching and =
is used for equality test (as defined by the COLLATION
in use).
=
can use indexes while LIKE
queries usually require testing every single record in the result set to filter it out (unless you are using full text search) so =
has better performance.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With