SQLTeam.com | Weblogs | Forums

Value in sql table column is strange

#1

Hi There,

I have data value in one of sql table column like this, for example, Supplier Group column,

Facilities

Office Supplies

Vendors A - L

Notice there is return and extra space in between, how do I get this extra return and extra space clear?

#2

Its probably Carriage Return and Line Feed characters - CHAR(13) and CHAR(10). You could REPLACE them with a space, or something else suitable

#3

Kristen,

Thanks! Yes, exactly. Is it something like this? but it seems not working for me
select replace([supplier group], char(13), ' ') from [dbo].[Supplier]

#4

You may need to replace not just char(13), but char(10) as well.

If that doesn't fix it, the characters that you are not seeing may be something else. Cast the column to varbinary and see what the characters are. For example, the following query (where I have appended AAAA to the end of the first line and to the beginning of the third line to make it easier on the eyes) shows that I have two pairs of 0D0A, i.e., char(13) and char(10).

DECLARE @x VARCHAR(256) = 
'FacilitiesAAAA

AAAAOffice';

SELECT CAST(@x AS VARBINARY(MAX));
SELECT REPLACE(REPLACE(@x,CHAR(10),''),CHAR(13),'');