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

avatar image

sqlLearner 1
972 39 51 57

(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

avatar image

Kev Riley ♦♦
64.2k 48 62 81

(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

avatar image

TimothyAWiseman
15.6k 22 51 38

  • 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

avatar image

xnl28
905 60 64 68

(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

avatar image

Pavel Pawlowski
22.7k 10 15 26

(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

avatar image

karthik84vb
28 2 3 5

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:

x393
x68

asked: Jun 14, 2012 at 02:13 PM

Seen: 2675 times

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

Copyright 2016 Redgate Software. Privacy Policy