using Between for characters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

using Between for characters

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-02-18 : 22:02:52

hi

This is my code:

SELECT * FROM Customers
where CustomerName Between 'J' and 'L'

I am only able to retrieve all the J and K but misses the L.

I know i have customers that are Jack, Kent, and Lance.

What went wrong? Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 00:00:10

may be this

SELECT * FROM Customers
where CustomerName LIKE '[J-L]%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-02-19 : 01:07:30

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 01:08:09

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 01:17:11

Note that this is the perfect example of Regular expression

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 02:17:01

SELECT * FROM Customers
where CustomerName Between 'J' and 'L'

would give you "L" but not a CustoemrName that starts with "L" - so "L" is the upper limit.

You could use "CustomerName Between 'J' and 'Lzzz' - provided you had noone with a name with a character "higher" the Lz", or you could do "CustomerName Between 'J' and 'M'" provided you had no one with a name of just "M"

Alternatively


SELECT * FROM Customers
where     CustomerName >= 'J'
      and CustomerName < 'M'

is the only way to get a range (so with dates that would be "Dates on-or-after XXXXX and before 'Tomorrow'" - slightly perverse logic!)

Visakh's way works fine too by testing the first character of the CustomerName (but that won't work for numbers or dates, if you have a need for that in the future)

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 02:21:26

"Note that this is the perfect example of Regular expression"

Most times it will work fine. I prefer the ">= ... <" approach as there is a risk that there are some characters in the RegEx [J-L] range that may be interpreted "unexpectedly" by collation. (Although that may be the case to for the limits for ">= ... <" I suppose - particularly Hyphen which is differently placed (Hi/Lo) in SQL and Windows collations I believe)

... I'll get my coat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources