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:
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:
Any suggestions on a function that can accomplish this?
asked Jun 14, 2012 at 02:13 PM in Default
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:
answered Jun 14, 2012 at 02:33 PM
Kev Riley ♦♦
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:
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.
answered Jun 14, 2012 at 04:18 PM
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:
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.
answered Jun 14, 2012 at 07:21 PM
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))
SELECT dbo.[UFN_RemoveMltSpace]('this is
answered Jun 15, 2012 at 06:27 AM