SQL: Using <= and >= to compare string with wildcard

user_3714598 0
Viewed 7727

Assuming I have table that looks like this:

Id  |  Name  |  Age
=====================
1   |  Jose  | 19

2   |  Yolly | 26

20  |  Abby  | 3

29  |  Tara  | 4

And my query statement is:

1) Select * from thisTable where Name <= '*Abby'; it returns 0 row

2) Select * from thisTable where Name <= 'Abby'; returns row with Abby

3) Select * from thisTable where Name >= 'Abby'; returns all rows // row 1-4

4) Select * from thisTable where Name >= '*Abby'; returns all rows; // row 1-4

5) Select * from thisTable where Name >= '*Abby' and Name <= "*Abby"; returns 0 row.

6) Select * from thisTable where Name >= 'Abby' and Name <= 'Abby'; returns row with Abby;

My question: why I got these results? How does the wildcard affect the result of query? Why don't I get any result if the condition is this Name <= '*Abby' ?

string-comparison wildcard sql-server sql
5 Answers

Wildcards are only interpreted when you use LIKE opterator.

So when you are trying to compare against the string, it will be treated literally. So in your comparisons lexicographical order is used.

1) There are no letters before *, so you don't have any rows returned.

2) A is first letter in alphabet, so rest of names are bigger then Abby, only Abby is equal to itself.

3) Opposite of 2)

4) See 1)

5) See 1)

6) This condition is equivalent to Name = 'Abby'.

user_71325500

A wildcard character is used to substitute any other characters in a string. They are used in conjunction with the SQL LIKE operator in the WHERE clause. For example.

Select * from thisTable WHERE name LIKE '%Abby%'

This will return any values with Abby anywhere within the string.

Have a look at this link for an explanation of all wildcards https://www.w3schools.com/sql/sql_wildcards.asp

user_100046210

When working with strings in SQL Server, ordering is done at each letter, and the order those letters are sorted in depends on the collation. For some characters, the sorting method is much easier to understand, It's alphabetical or numerical order: For example 'a' < 'b' and '4' > '2'. Depending on the collation this might be done by letter and then case ('AaBbCc....') or might be Case then letter ('ABC...Zabc').

Let's take a string like 'Abby', this would be sorted in the order of the letters A, b, b, y (the order they would appear would be according to your collation, and i don't know what it is, but I'm going to assume a 'AaBbCc....' collation, as they are more common). Any string starting with something like 'Aba' would have a value sell than 'Abby', as the third character (the first that differs) has a "lower value". As would a value like 'Abbie' ('i' has a lower value than 'y'). Similarly, a string like 'Abc' would have a greater value, as 'c' has a higher value than 'b' (which is the first character that differs).

If we throw numbers into the mix, then you might be surpised. For example the string (important, I didn't state number) '123456789' has a lower value than the string '9'. This is because the first character than differs if the first character. '9' is greater than '1' and so '9' has the "higher" value. This is one reason why it's so important to ensure you store numbers as numerical datatypes, as the behaviour is unlikely to be what you expect/want otherwise.

To what you are asking, however, the wildcard for SQL Server is '%' and '_' (there is also '^',m but I won't cover that here). A '%' represents multiple characters, while '_' a single character. If you want to specifically look for one of those character you have to quote them in brackets ([]).

Using the equals (=) operator won't parse wildcards. you need to use a function that does, like LIKE. Thus, if you want a word that started with 'A' you would use the expression WHERE ColumnName LIKE 'A%'. If you wanted to search for one that consisted of 6 characters and ended with 'ed' you would use WHERE ColumnName LIKE '____ed'.

Like I said before, if you want to search for one of those specific character, you quote then. So, if you wanted to search for a string that contained an underscore, the syntax would be WHERE ColumnName LIKE '%[_]%'

Edit: it's also worth noting that, when using things like LIKE that they are effected by the collations sensitivity; for example, Case and Accent. If you're using a case sensitive collation, for example, then the statement WHERE 'Abby' LIKE 'abb%' is not true, and 'A' and 'a' are not the same case. Like wise, the statement WHERE 'Covea' = 'Covéa' would be false in an accent sensitive collation ('e' and 'é' are not treated as the same character).

user_20299830

It is because, >= and <= are comparison operators. They compare string on the basis of their ASCII values.

Since ASCII value of * is 42 and ASCII values of capital letters start from 65, that is why when you tried name<='*Abby', sql-server picked the ASCII value of first character in your string (that is 42), since no value in your data has first character with ASCII value less than 42, no data got selected.

You can refer ASCII table for more understanding:

http://www.asciitable.com/

user_76718760

There are a few answers, and a few comments - I'll try to summarize.

Firstly, the wildcard in SQL is %, not * (for multiple matches). So your queries including an * ask for a comparison with that literal string.

Secondly, comparing strings with greater/less than operators probably does not do what you want - it uses the collation order to see which other strings are "earlier" or "later" in the ordering sequence. Collation order is a moderately complex concept, and varies between machine installations.

The SQL operator for string pattern matching is LIKE.

I'm not sure I understand your intent with the >= or <= stateements - do you mean that you want to return rows where the name's first letter is after 'A' in the alphabet?

user_6266920
Related