Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I am learning basics of SQL through W3School and during understanding basics of wildcards I went through the following query:

--Finds any values that start with "a" and are at least 3 characters in length
WHERE CustomerName LIKE 'a_%_%'

as per the example following query will search the table where CustomerName column start with 'a' and have at least 3 characters in length.

However, I try the following query also:

WHERE CustomerName LIKE 'a__%'

The above query also gives me the exact same result. I want to know whether there is any difference in both queries? Does the second query produce a different output in some specific scenario? If yes what will be that scenario?

like image 537
Chetan Mehra Avatar asked Nov 27 '17 07:11

Chetan Mehra


People also ask

What is difference between <> and !=?

Difference between SQL Not Equal Operator <> and != to do inequality test between two expressions. Both operators give the same output. The only difference is that '<>' is in line with the ISO standard while '!= ' does not follow ISO standard.

What is difference between and @@ in SQL Server?

There is no difference. The rules for variables state that they start with an '@' character and follow the rules for identifiers. Since '@' is a valid identifier character, you can have as many as you like at the start of your variable name.

Is underscore a wildcard in SQL?

Summary. To broaden the selections of a structured query language (SQL-SELECT) statement, two wildcard characters, the percent sign (%) and the underscore (_), can be used. The percent sign is analogous to the asterisk (*) wildcard character used with MS-DOS.

What is the difference between MS SQL Server and SQL?

MS SQL server is also available in multiple editions that include feature sets customized for different users. To summarize, SQL is a computer language for creating and managing relational databases and Microsoft SQL server is a database server that uses SQL as its primary query language and it can be used to develop database applications.

What is SQL Server and how it works?

It is an application that stores the database data and executes the SQL commands and queries to manipulate the relational database. Moreover, it also manages and performs all the database operations. The SQL Server is developed by Microsoft in the year 1989 for commercial purposes.

What is the difference between = and in operator in SQL?

In this article we are going to see the difference between = and IN operator in SQL. The = operator is used with Where Clause in SQL. To fetch record of students with address as Delhi or ROHTAK. 2. IN Operator : The IN operator is used with Where Clause to test if the expression matches any value in the list of values.

What is the difference between @table and # #table in SQL Server?

In SQL Server, what is the difference between a @ table, a # table and a ## table? #table refers to a local (visible to only the user who created it) temporary table. ##table refers to a global (visible to all users) temporary table. @variableName refers to a variable which can hold values depending on its type.


2 Answers

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?

like image 62
KtX2SkD Avatar answered Oct 21 '22 12:10

KtX2SkD


For Like operator a single underscore "_" means, any single character, so if you put One underscore like

ColumnName LIKE 'a_%'

you basically saying you need a string where first letter is 'a' then followed by another single character and then followed by anything or nothing.

ColumnName LIKE 'a__%' OR ColumnName LIKE 'a_%_%'

Both expressions mean first letter 'a' then followed by two characters and then followed by anything or nothing. Or in simple English any string with 3 or more character starting with a.

like image 38
M.Ali Avatar answered Oct 21 '22 11:10

M.Ali