Error in returning values when using >=

if i have the following data:
Name
--
SILFL-CFL-ELI24-220-GR
SILFL-CFL-ELI24-220-WH
SILFL-CFL-TOP-GLASS
SILFL-CFL-TOP-LOUVER
SILFL-CFL-TOP218-WH
SILFL-CFL-TOP226-WH
SILFL-FLU-EST18-220-WH
SILFL-FLU-EST36-220-WH
SILFL-FLU-FLA08-220-WH
SILFL-FLU-FLA13-220-WH
SILFL-FLU-MAR/P-218-220
SILFL-FLU-MAR/P-236-220-EB
SILFL-FLU-MAR/P-236-220-EES
SILFL-FLU-MAR/P-236-220
SILFL-FLU-STY108-220-WH
SILFL-FLU-STY113-220-WH
SILFL-FLU-STY15-220-BK
SILFL-FLU-STY15-220-WH
SILFL-FLU-STY18-220-BK
SILFL-FLU-STY18-220-WH
SILFL-FLU-STY30-220-BK
SILFL-FLU-STY30-220-WH
SILFL-FLU-STY36-220-BK
SILFL-FLU-STY36-220-WH
SILFL-GLS-GLOB-220-BK
SILFL-GLS-GLOB-220-WH
SILFL-GLS-ROLL-220-WH
SILFL-GLS-ROLL-220V-BK
SILFL-HPD-INT-ARCLT-150-WH
- When i run the following query no data is returned:
Select *
from TableName
where Name >= 'SILFL'
- If I add a dash the correct data is returned.
Select *
from TableName
where Name >= 'SILFL-'
Why is SQL Server seeing the string 'SILFL' as smaller than 'SILFL-'I believe that this is to do with the collation you have on your server.
Out of interest I did the same thing and got back the correct results.
You can probably get back the correct results by doing the following...
SELECT *
FROM YourTable
WHERE cast(Info as nvarchar(100)) >= N'S'
"Happiness is having a large, loving, caring, close-knit family in another
city."
George Burns
Anyway have a look at your collation and read about it on BOL.
Peter
"Nadim Wakim" wrote:

> if i have the following data:
> Name
> --
> SILFL-CFL-ELI24-220-GR
> SILFL-CFL-ELI24-220-WH
> SILFL-CFL-TOP-GLASS
> SILFL-CFL-TOP-LOUVER
> SILFL-CFL-TOP218-WH
> SILFL-CFL-TOP226-WH
> SILFL-FLU-EST18-220-WH
> SILFL-FLU-EST36-220-WH
> SILFL-FLU-FLA08-220-WH
> SILFL-FLU-FLA13-220-WH
> SILFL-FLU-MAR/P-218-220
> SILFL-FLU-MAR/P-236-220-EB
> SILFL-FLU-MAR/P-236-220-EES
> SILFL-FLU-MAR/P-236-220
> SILFL-FLU-STY108-220-WH
> SILFL-FLU-STY113-220-WH
> SILFL-FLU-STY15-220-BK
> SILFL-FLU-STY15-220-WH
> SILFL-FLU-STY18-220-BK
> SILFL-FLU-STY18-220-WH
> SILFL-FLU-STY30-220-BK
> SILFL-FLU-STY30-220-WH
> SILFL-FLU-STY36-220-BK
> SILFL-FLU-STY36-220-WH
> SILFL-GLS-GLOB-220-BK
> SILFL-GLS-GLOB-220-WH
> SILFL-GLS-ROLL-220-WH
> SILFL-GLS-ROLL-220V-BK
> SILFL-HPD-INT-ARCLT-150-WH
> - When i run the following query no data is returned:
> Select *
> from TableName
> where Name >= 'SILFL'
>
> - If I add a dash the correct data is returned.
> Select *
> from TableName
> where Name >= 'SILFL-'
> Why is SQL Server seeing the string 'SILFL' as smaller than 'SILFL-'
>
>|||Becuase it is. Anything with 4 characters is "smaller" than the same thing
with any character added at the end.
abcd' < 'abcd' + 'a'
What I Don't understand is why the query is not returning anything. All
those values are > 'SILFL', because 'SILFL' is < than all of them.
When I run the following, ALL The records are returned...
Create Table Test (Col VarCHar(50))
Insert Test (Col) VAlues ('SILFL-CFL-ELI24-220-GR')
Insert Test (Col) VAlues ('SILFL-CFL-ELI24-220-WH')
Insert Test (Col) VAlues ('SILFL-CFL-TOP-GLASS')
Insert Test (Col) VAlues ('SILFL-CFL-TOP-LOUVER')
Insert Test (Col) VAlues ('SILFL-CFL-TOP218-WH')
Insert Test (Col) VAlues ('SILFL-CFL-TOP226-WH')
Insert Test (Col) VAlues ('SILFL-FLU-EST18-220-WH')
Insert Test (Col) VAlues ('SILFL-FLU-EST36-220-WH')
Insert Test (Col) VAlues ('SILFL-FLU-FLA08-220-WH')
Insert Test (Col) VAlues ('SILFL-FLU-FLA13-220-WH')
Insert Test (Col) VAlues ('SILFL-FLU-MAR/P-218-220')
Insert Test (Col) VAlues ('SILFL-FLU-MAR/P-236-220-EB')
Insert Test (Col) VAlues ('SILFL-FLU-MAR/P-236-220-EES')
Insert Test (Col) VAlues ('SILFL-FLU-MAR/P-236-220')
Insert Test (Col) VAlues ('SILFL-FLU-STY108-220-WH')
Insert Test (Col) VAlues ('SILFL-FLU-STY113-220-WH')
Insert Test (Col) VAlues ('SILFL-FLU-STY15-220-BK')
Insert Test (Col) VAlues ('SILFL-FLU-STY15-220-WH')
Insert Test (Col) VAlues ('SILFL-FLU-STY18-220-BK')
Insert Test (Col) VAlues ('SILFL-FLU-STY18-220-WH')
Insert Test (Col) VAlues ('SILFL-FLU-STY30-220-BK')
Insert Test (Col) VAlues ('SILFL-FLU-STY30-220-WH')
Insert Test (Col) VAlues ('SILFL-FLU-STY36-220-BK')
Insert Test (Col) VAlues ('SILFL-FLU-STY36-220-WH')
Insert Test (Col) VAlues ('SILFL-GLS-GLOB-220-BK')
Insert Test (Col) VAlues ('SILFL-GLS-GLOB-220-WH')
Insert Test (Col) VAlues ('SILFL-GLS-ROLL-220-WH')
Insert Test (Col) VAlues ('SILFL-GLS-ROLL-220V-BK')
Insert Test (Col) VAlues ('SILFL-HPD-INT-ARCLT-150-WH')
Select * from Test
where Col >= 'SILFL-'
"Nadim Wakim" wrote:

> if i have the following data:
> Name
> --
> SILFL-CFL-ELI24-220-GR
> SILFL-CFL-ELI24-220-WH
> SILFL-CFL-TOP-GLASS
> SILFL-CFL-TOP-LOUVER
> SILFL-CFL-TOP218-WH
> SILFL-CFL-TOP226-WH
> SILFL-FLU-EST18-220-WH
> SILFL-FLU-EST36-220-WH
> SILFL-FLU-FLA08-220-WH
> SILFL-FLU-FLA13-220-WH
> SILFL-FLU-MAR/P-218-220
> SILFL-FLU-MAR/P-236-220-EB
> SILFL-FLU-MAR/P-236-220-EES
> SILFL-FLU-MAR/P-236-220
> SILFL-FLU-STY108-220-WH
> SILFL-FLU-STY113-220-WH
> SILFL-FLU-STY15-220-BK
> SILFL-FLU-STY15-220-WH
> SILFL-FLU-STY18-220-BK
> SILFL-FLU-STY18-220-WH
> SILFL-FLU-STY30-220-BK
> SILFL-FLU-STY30-220-WH
> SILFL-FLU-STY36-220-BK
> SILFL-FLU-STY36-220-WH
> SILFL-GLS-GLOB-220-BK
> SILFL-GLS-GLOB-220-WH
> SILFL-GLS-ROLL-220-WH
> SILFL-GLS-ROLL-220V-BK
> SILFL-HPD-INT-ARCLT-150-WH
> - When i run the following query no data is returned:
> Select *
> from TableName
> where Name >= 'SILFL'
>
> - If I add a dash the correct data is returned.
> Select *
> from TableName
> where Name >= 'SILFL-'
> Why is SQL Server seeing the string 'SILFL' as smaller than 'SILFL-'
>
>