CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mr. Vetter
Mr. Vetter

Reputation: 21

Ignored characters in Where-Clause?

I have the following problem:

I try to select everything below 'G-' but as it seems it ignores the - and selects everything with G and below.

select * from tableA where ColumnA > 'G-' order by ColumnA

If i use > 'G-T' it ignores the '-' totally, giving me things like 'GT...'

Upvotes: 2

Views: 155

Answers (3)

Luaan
Luaan

Reputation: 63732

The problem is with your collation - you're using a text collation for ordering of what appears to be non-text data. Given your requirements, I'd suggest using Latin1_General_Binary as the column's collation - provided the data really is strongly formed, and not "human-like".

For example, for values of 

G-
G 22
G-T
GT

the ordering in Latin1_General_Binary is

G 22
G-
G-T
GT

In Latin1_General_CI_AS it's 

G-
G 22
GT
G-T

The basic reason for this is that - is considered an ignorable character; the purpose of this is to sort co-operation near cooperation, for example. This is very important for text sorting - for example, in czech, Ch is considered a single letter (after H, not C), so even though it's written as two separate letters in Latin2, the sorting must take this into account.

GT being larger than G-T is somewhat unavoidable - symbols and control characters tend to be located before numbers and letters in most collations. How much this bothers you depends on your requirements - if the form A-B is something special for you, you could use something like this:

where A like `.-%` and A > `G-`

If it's not a problem to you, though, I'd avoid forcing a non-trivial ordering - your indices will thank you.

If the pattern isn't as simple as this, you're basically in a world of hurt - SQL databases rely quite a bit on having normalised data, and by this point, your ID no longer is a single logical column. Separating it into multiple columns might work, depending again on your requirements.

Upvotes: 1

xlecoustillier
xlecoustillier

Reputation: 16351

The sorting value of '-' is lower than the one from 'T' so 'G-' is lower than 'GT'.

So

select * from tableA where ColumnA > 'G-' order by ColumnA

will return 'GT', but

select * from tableA where ColumnA < 'G-' order by ColumnA

won't. '-' is not ignored, it's just lower than 'T'.

Of course, the same goes if you try using ColumnA > 'G-T', for the same reasons.

If you want to select everything that starts with 'G-', use LIKE:

select * from tableA where ColumnA LIKE 'G-%' order by ColumnA

Upvotes: 2

TechTalk
TechTalk

Reputation: 51

Use 'like' operator.

Create table vstab
(
col1 varchar(100)
)

insert into vstab
Select 'G-1'

insert into vstab
Select 'G-2'

insert into vstab
Select 'G1'

insert into vstab
Select 'G2'

Select * from VSTab where col1 like 'G-%' order by col1

Upvotes: 0

Related Questions