String Cleanup Function

I have a tab delmited text file So of the records span over 2 lines. So when I import into Management studio It puts a bunch of SPACES in between. I am trying to create a function that that will eliminate the MULTIPLE SPACES and just provide a single space:'

Below IS an example Notice how "This is a test" is spread across 2 lines:

"123456"    "" "This is
 a test"    

I need a function that makes it read 'This is a test' in the database.

How it imports I would need to use the following select statement to get a result:

Select * from tableA Where term = "This is
 a test"
Any suggestions on a function that can accomplish this?
more ▼

asked Jun 14, 2012 at 02:13 PM in Default

sqlLearner 1 gravatar image

sqlLearner 1
802 36 43 47

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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), '')
more ▼

answered Jun 14, 2012 at 02:33 PM

Kev Riley gravatar image

Kev Riley ♦♦
54k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jun 14, 2012 at 04:18 PM

TimothyAWiseman gravatar image

15.6k 21 23 32

+1 Tim: I'm sure Mrs Kev would disagree that 'as usual' I'm right :) !!
Jun 14, 2012 at 08:19 PM Kev Riley ♦♦
Heh. Well, I'm confident you are usually right about SQL at least. In anything else, I'll defer to her ;)
Jun 18, 2012 at 07:07 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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), '')
more ▼

answered Jun 14, 2012 at 02:30 PM

xnl28 gravatar image

895 56 60 62

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jun 14, 2012 at 07:21 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

Try it.....


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
    SET @li_ChrPos=CHARINDEX(' ',@pi_StrData,@li_ChrPos)

    IF @li_ChrPos>0
       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
    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

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

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



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

answered Jun 15, 2012 at 06:27 AM

karthik84vb gravatar image

28 1 3 4

Gosh no. There's just no need for a While Loop here. Use nested REPLACEs instead.
Jun 16, 2012 at 02:15 AM Jeff Moden
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 14, 2012 at 02:13 PM

Seen: 1947 times

Last Updated: Jun 18, 2012 at 07:07 PM