String Cleanup Function

SSMS doesn't do word wrap, so if it's showing like that then it probably contains a line-feed. You can replace them like this:

declare @test varchar(max)

set @test = 'this is 
a test'

select replace(@test, char(13)+char(10), '')

Kev, as usual, is completely right. But I think there are a couple of things I could add.

For one, sometimes you will just find CR or LF as the end of line character instead of both together. This would be somewhat unusual in Windows, but it shows up occassionally if you are working in a multi-operating system environment or using multi-operating system tools like SciTE. If you want to cover this possibility you could use:

replace(replace(@test, char(13), ''), char(10), '')

Also, depending on the workflow it may be worth making adjustments like this prior to importing the file into SQL. SQL's text manipulation tools are sufficient for many things, but notoriously ineloquent when compared to more general purpose langauges like C# or Python.

If the data in the field term contains carriage-return and line-feed characters, then you can remove them using REPLACE.

For example, after importing your data you could run this command:

update TableA set term = replace(term, CHAR(13)+CHAR(10), '')

How you are importing the tab delimited file?

If you are pasting it into SSMS and than processing it in some way, than that's not an optimal way.

If you use BCP or SSIS (Import/Export wizard which will create a SSIS for you), then you should not have any problems with importing that file and the text should not be spread in multiple lines in the destination table.

Try it.....

CREATE FUNCTION [dbo].[UFN_RemoveMltSpace](@pi_StrData VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @li_Pos INT ,@li_ChrLastPos INT ,@li_ChrPos INT ,@li_FNOutput VARCHAR(MAX)='' DECLARE @li_tblRmvSpace TABLE(ID INT,ColVal VARCHAR(MAX))

SET @li_Pos=1
SET @li_ChrLastPos=0
SET @li_ChrPos=1
SET @pi_StrData=REPLACE(@pi_StrData,CHAR(13)+CHAR(10),' ')

WHILE @li_Pos <> 0
BEGIN
    SET @li_ChrPos=CHARINDEX(' ',@pi_StrData,@li_ChrPos)

    IF @li_ChrPos>0
    BEGIN
       IF LEN(SUBSTRING(@pi_StrData,@li_ChrLastPos,@li_ChrPos-@li_ChrLastPos))>0
         INSERT INTO @li_tblRmvSpace VALUES (@li_Pos,SUBSTRING(@pi_StrData,@li_ChrLastPos,@li_ChrPos-@li_ChrLastPos))
       SET @li_Pos=@li_Pos+1
    END
    ELSE
    BEGIN
    IF LEN(SUBSTRING(@pi_StrData,@li_ChrLastPos,LEN(@pi_StrData)))>0
       INSERT INTO @li_tblRmvSpace VALUES (@li_Pos,SUBSTRING(@pi_StrData,@li_ChrLastPos,LEN(@pi_StrData)))
       SET @li_Pos=0
    END

    SET @li_ChrPos=@li_ChrPos+1
    SET @li_ChrLastPos=@li_ChrPos
END

SELECT @li_FNOutput=@li_FNOutput+ ColVal + ' ' FROM @li_tblRmvSpace

RETURN RTRIM(@li_FNOutput)

END GO

SELECT dbo.[UFN_RemoveMltSpace]('this is
a test')