It took a little effort, but here's a replace function that will do the job and more. Won't swear it can't be improved upon or what kind of performance curve it may have. For this small example it's quick. But consider it nothing more than a first draft. If you have thoughts of using it in a production environment you should certainly do some stress testing since it's a scalar function. If you are going to use it (for example) to just import data once a day then it should work just fine. I just wouldn't stick it in the SELECT statement of a heavily-used query in production without testing it thoroughly.

EDIT: I read your notes above after posting this. The function should work fine as it is, but you might need to rearrange a few columns in the pivot table query to get exactly what you need.

CREATE FUNCTION dbo.svfSplitReplaceByDelimiter

(

@STR VARCHAR(8000)

,@delimiter CHAR(1)

,@subchar1 CHAR(1) = '@'

,@subchar2 CHAR(1) = '~'

,@replaceval VARCHAR(50) = '|'

)

RETURNS VARCHAR(MAX)

AS

BEGIN

/*

Parameters:

@STR VARCHAR(8000) -- the input string

,@delimiter CHAR(1) -- the delimiter to use for the split

,@subchar1 CHAR(1) = '@' -- pick a character not in the input string (internal use)

,@subchar2 CHAR(1) = '~' -- pick a character not in the input string (internal use)

,@replaceval VARCHAR(50) = '|' -- the delimiter used for the final array; can be up to 50 chars

per tag and can be useful for building XML or HTML strings

*/

DECLARE

@pos INT

,@len INT

,@strRep VARCHAR(8000)

,@strTemp VARCHAR(8000)

,@strRFrag VARCHAR(50)

,@strOut VARCHAR(8000)

,@counter INT

SET @STR = RTRIM(LTRIM(@str))

SET @strRep = REPLACE(@str,@delimiter,@subchar1)

SET @strTemp = @strRep

SET @strOut = ''

--get the last chunk for use later

SET @pos = CHARINDEX(@subchar1,REVERSE(@strTemp))-1

SET @strRFrag = RIGHT(@strTemp,@pos)

--start looking for single characters

--and if found aggregate them

SET @counter = 1

SET @pos = CHARINDEX(@subchar1,@strRep)

SET @len = LEN(@strTemp)

WHILE @counter <= @len

BEGIN

SET @strTemp = LEFT(@strRep,@pos)

IF RIGHT(@strTemp,1) = @subchar1

BEGIN

IF @pos = 2

SET @strTemp = REPLACE(@strTemp,@subchar1,@subchar2)

ELSE

SET @strTemp = REPLACE(@strTemp,@subchar1,@delimiter)

IF @pos > 2

SET @strOut = @strOut + @delimiter + @strTemp

ELSE

SET @strOut = @strOut + @strTemp

IF RIGHT(@strOut,1) = @subchar2

SET @strOut = LEFT(@strOut,LEN(@strOut)-1)

SET @pos = CHARINDEX(@subchar1,@strRep)

SET @strRep = RIGHT(@strRep,LEN(@strRep)-@pos)

END

SET @pos = CHARINDEX(@subchar1,@strRep)

SET @counter = @counter + 1

END

IF LEN(@strRFrag) = 1

SET @strOut = @strOut + @strRFrag

ELSE

SET @strOut = @strOut + @delimiter + @strRFrag

SET @strOut = REPLACE(REPLACE(@strOut,@delimiter+@delimiter,@delimiter),' ',' ')

SET @strOut = REPLACE(REPLACE(@strOut,@delimiter,@replaceval),' ',' ')

IF LEFT(@strOut,1) = @delimiter

SET @strOut = RIGHT(@strOut,LEN(@strOut)-1)

IF RIGHT(@strOut,1) = @delimiter

SET @strOut = LEFT(@strOut,LEN(@strOut)-1)

SET @strOut = RTRIM(LTRIM(@strOut))

RETURN @strOut

END

GO

Now load the sample data and test the function. (Requires the function DelimitedSplit8K which can be found by searching this site. It's been posted so many times and it's such an indispensable function that I figure most people already have it.)

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[OrigCompanyName] NVARCHAR(150) NULL,

[CompanyName] NVARCHAR(150) NULL,

PRIMARY KEY (ID))

;WITH cteOriginal(CompanyName,CompName1,CompName2,CompName3)

AS (

SELECT

'A V H S L ALQUILER Y VENTA DE HOGARES'

,NULL

,NULL

,NULL

UNION ALL

SELECT

'TAXI GIL PIERRE'

,NULL

,NULL

,NULL

UNION ALL

SELECT

'DE GOUDSMID M HEIJKOOP'

,NULL

,NULL

,NULL

UNION ALL

SELECT

'S P S S'

,NULL

,NULL

,NULL

UNION ALL

SELECT

'BOUCHERIE CHARCUTERIE ST HENRI'

,NULL

,NULL

,NULL

UNION ALL

SELECT

'A A M RODERKERKEN'

,NULL

,NULL

,NULL

UNION ALL

SELECT

'O MUNDO E A NOSSA CASA - C H E'

,NULL

,NULL

,NULL

)

INSERT INTO #TempTable

SELECT

CompanyName AS OrigCompanyName

,(SELECT dbo.svfSplitReplaceByDelimiter(cte.CompanyName,' ','@','~','|')) AS CompanyName

FROM

cteOriginal AS cte

--get rid of stray leading and trailing characters

UPDATE #TempTable

SET CompanyName =

(CASE

WHEN LEFT(CompanyName,1) = '|' THEN RIGHT(CompanyName,LEN(CompanyName)-1)

WHEN RIGHT(CompanyName,1) = '|' THEN LEFT(CompanyName,LEN(CompanyName)-1)

ELSE CompanyName

END)

--create the pivot table result

SELECT

r1.ID

,(SELECT OrigCompanyName FROM #TempTable WHERE ID = r1.ID) AS CompanyName

,r1.Part1

,r1.Part2

,r1.Part3

,r1.Part4

,r1.Part5

,r1.Part6

FROM

(

SELECT

r.ID

,MAX(CASE WHEN r.ItemNumber = 1 THEN r.Item END) AS Part1

,MAX(CASE WHEN r.ItemNumber = 2 THEN r.Item END) AS Part2

,MAX(CASE WHEN r.ItemNumber = 3 THEN r.Item END) AS Part3

,MAX(CASE WHEN r.ItemNumber = 4 THEN r.Item END) AS Part4

,MAX(CASE WHEN r.ItemNumber = 5 THEN r.Item END) AS Part5

,MAX(CASE WHEN r.ItemNumber = 6 THEN r.Item END) AS Part6

FROM

(

SELECT

ID

,ItemNumber

,Item

FROM

#TempTable AS tt

CROSS APPLY

dbo.DelimitedSplit8K(tt.CompanyName,'|') AS dsk1

WHERE

dsk1.Item <> ''

) r

GROUP BY

r.ID

) r1

The output:

IDCompanyNamePart1Part2Part3Part4Part5Part6

1A V H S L ALQUILER Y VENTA DE HOGARESAVHSLALQUILERYVENTADEHOGARES

2TAXI GIL PIERRETAXIGILPIERRENULLNULLNULL

3DE GOUDSMID M HEIJKOOPDEGOUDSMIDMHEIJKOOPNULLNULL

4S P S SSPSSNULLNULLNULLNULLNULL

5BOUCHERIE CHARCUTERIE ST HENRIBOUCHERIECHARCUTERIESTHENRINULLNULL

6A A M RODERKERKENAAMRODERKERKENNULLNULLNULLNULL

7O MUNDO E A NOSSA CASA - C H EOMUNDOEANOSSACASA-CHE