x

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 44 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 ♦♦
54.3k 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

TimothyAWiseman
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

xnl28
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.....

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

answered Jun 15, 2012 at 06:27 AM

karthik84vb gravatar image

karthik84vb
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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x293
x58

asked: Jun 14, 2012 at 02:13 PM

Seen: 1969 times

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