rgb192
asked on
sort text column
CREATE TABLE [dbo].[sitedata](
[productid] [int] NULL,
[pathid] [int] NULL,
[accessoryid] [int] NULL,
[alternatecolors] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[specifications] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[features] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[itemincludes] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[accessories] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[infoandguides] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[rebateinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[sunshineactive] [int] NULL,
[sunshineprice] [money] NULL,
[sunshinefeed] [int] NULL,
[pyxisactive] [int] NULL,
[pyxisprice] [money] NULL,
[pyxisfeed] [int] NULL,
[hdewactive] [int] NULL,
[hdewprice] [money] NULL,
[hdewfeed] [int] NULL,
[shippingtype] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[shippingpriceground] [money] NULL,
[shippingprice3day] [money] NULL,
[shippingprice2day] [money] NULL,
[shippingprice1day] [money] NULL,
[shippingpricetruck] [money] NULL,
[shippingpricetruckexpress ] [money] NULL,
[shippingpricetruckwhite] [money] NULL,
[pyxispackages] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[sunpackages] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[ebaydescription] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[pdescription] [text] COLLATE SQL_Latin1_General_CP1_CI_ AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
select * from sitedata
order by accessories desc
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
select * from sitedata
where accessories are not null
Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near 'are'.
this is what one column has it in it for one row
147276,146588,147137,14658 6,147371,1 48941,1489 42,148943, 148944,148 945,3770,1 48951,2057 ,148899,14 8934,14893 8,3652,148 918,148914 ,148921,31 701,147445 ,148929,14 8930,14892 7,148928,1 48926,53,2 1063,14606 6,147141,1 47213,1473 72,147373, 147290,145 762,1079,5 7,147191,1 47423,54,5 5,147139
[productid] [int] NULL,
[pathid] [int] NULL,
[accessoryid] [int] NULL,
[alternatecolors] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_
[specifications] [text] COLLATE SQL_Latin1_General_CP1_CI_
[features] [text] COLLATE SQL_Latin1_General_CP1_CI_
[itemincludes] [text] COLLATE SQL_Latin1_General_CP1_CI_
[accessories] [text] COLLATE SQL_Latin1_General_CP1_CI_
[infoandguides] [text] COLLATE SQL_Latin1_General_CP1_CI_
[rebateinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_
[sunshineactive] [int] NULL,
[sunshineprice] [money] NULL,
[sunshinefeed] [int] NULL,
[pyxisactive] [int] NULL,
[pyxisprice] [money] NULL,
[pyxisfeed] [int] NULL,
[hdewactive] [int] NULL,
[hdewprice] [money] NULL,
[hdewfeed] [int] NULL,
[shippingtype] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[shippingpriceground] [money] NULL,
[shippingprice3day] [money] NULL,
[shippingprice2day] [money] NULL,
[shippingprice1day] [money] NULL,
[shippingpricetruck] [money] NULL,
[shippingpricetruckexpress
[shippingpricetruckwhite] [money] NULL,
[pyxispackages] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_
[sunpackages] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_
[ebaydescription] [text] COLLATE SQL_Latin1_General_CP1_CI_
[pdescription] [text] COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
select * from sitedata
order by accessories desc
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
select * from sitedata
where accessories are not null
Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near 'are'.
this is what one column has it in it for one row
147276,146588,147137,14658
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
You can not sort using a field of type [text]
You can change the field to a long varchar