How am I going to use BETWEEN Operator with Text Value or what is the right syntax when you will select all products with a ProductName for example ending with any of the letter BETWEEN 'C' and 'M'?
3
-
1Please tag your question with the database you are using.Gordon Linoff– Gordon Linoff2015-06-30 12:25:24 +00:00Commented Jun 30, 2015 at 12:25
-
1If this is going to be a common query, it may be worth investigating whether your database system (e.g. SQL Server, mysql, Oracle, etc) has features that would make an index possible for this query - e.g. function-based indexes or indexed computed columns. To know that, though, we'd need to know which system you're using.Damien_The_Unbeliever– Damien_The_Unbeliever2015-06-30 12:35:17 +00:00Commented Jun 30, 2015 at 12:35
-
I'm trying to code it on w3schools code generatorRicardo– Ricardo2015-06-30 12:37:16 +00:00Commented Jun 30, 2015 at 12:37
Add a comment
|
2 Answers
Most SQL dialects provide the RIGHT() function. This allows you to do:
WHERE RIGHT(TextValue, 1) BETWEEN 'C' AND 'M'
If your database doesn't have this function, you can do something similar with the built-in functions. Also, the exact comparison might depend on the collation of the column/table/database/server. Sometimes comparisons are independent of case and sometimes they are dependent on case.
4 Comments
Ricardo
Why there's an error occured when I ran it on w3sschools?
Ricardo
Seems like it's correct but it's not running on w3Schools.
jarlh
Seems like the w3sschools dbms doesn't support the RIGHT function.
Gordon Linoff
@user3783598 . . . It looks like they are running Oracle on the backend. The equivalent in Oracle is:
substr(customername, length(customername), 1).In case you are interested in an alternative method (which does work with the w3schools SQL editor), you can also use the LIKE operator:
WHERE ProductName LIKE '%[c-m]'
This will get you all Product Names ending on any character between C and M. (It does work with the w3schools SQL Editor.)
In this case, the LIKE operator is using two wildcard characters:
1.%
Any string of zero or more characters.
2.[c-m]
Any single character within the specified range ([a-f]) or set ([abcdef]).
You can find more information about the LIKE operator here:
https://msdn.microsoft.com/en-us/library/ms179859.aspx