Select Distinct ColumnName From Table produces error I believe due to spaces, tabs or carriage returns

I'm running a query against one of our tables.
Select Distinct ColumnName From Table. (SELECT DISTINCT FULLERROR
FROM [DMS].[dbo].[SD_LOGDATA])
When I run this command it produces the error "The text data type cannot be selected as DISTINCT because it is not comparable."
There are these funny looking squares in the FULLERROR Column and I believe these may be spaces, tabs or carriage returns.
Does anyone know what command I should run or if there's a way to change something up on the table to fix this?
Regards,
Wally
wally_davisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
It's not due to the carriage returns.  It's due to the data type -- TEXT.

Are you allowed to change data types?  If so, change to Varchar(Max) -- new for SQL 2005.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrandonGalderisiCommented:
OR... since changing the data type of a column can have massive ramifications, CAST the column in your select.

SELECT DISTINCT cast(FULLERROR as nvarchar(max))
FROM [DMS].[dbo].[SD_LOGDATA]
0
wally_davisAuthor Commented:
I used varchar in place of nvarchar(max)). So, yes we can change the datatype and we eventually will. both are great solutions and will award you each 250 points.
0
BrandonGalderisiCommented:
the problem with varchar is that varchar, without specifying a length, will default to 30.

So use varchar(max) instead of nvarchar(max) if you have a text column isntead of ntext.  But you should use max probably.  Why else would it be a TEXT column if it didn't house long data?
0
wally_davisAuthor Commented:
Good point Brandon. Well, someone else on my team assigned Text datatype to this column that will hold a large amount of data when they should have assigned datatype varchar.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.