Hi

This isn't exactly pretty ... but it's another way to skin the cat

;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)

-- Compress up the first single letters in the string

,preprocess as (

select CompanyName

,stuff(

CompanyName

,1

,isnull(nullif(patindex('% [a-z][a-z]%',substring(companyname,1,8)),0),isnull(nullif(charindex(' ',CompanyName,7),0),999))

,replace(substring(companyname,1,isnull(nullif(patindex('% [a-z][a-z]%',substring(companyname,1,8)),0),isnull(nullif(charindex(' ',CompanyName,7),0),999))),' ','') + ' '

) StuffedCompanyName

from cteOriginal

)

-- extract each company name in turn.

,r1 as (

select CompanyName

,rtrim(substring(StuffedCompanyName,1,isnull(nullif(charindex(' ',StuffedCompanyName),0),999))) Company1

,ltrim(substring(StuffedCompanyName,isnull(nullif(charindex(' ',StuffedCompanyName),0),999),999)) leftover

from preprocess

)

,r2 as (

select CompanyName

,Company1

,nullif(rtrim(substring(leftover,1,isnull(nullif(charindex(' ',leftover),0),999))),'') Company2

,ltrim(substring(leftover,isnull(nullif(charindex(' ',leftover),0),999),999)) leftover

from r1

)

,r3 as (

select CompanyName

,Company1

,Company2

,nullif(rtrim(substring(leftover,1,isnull(nullif(charindex(' ',leftover),0),999))),'') Company3

from r2

)

select * from r3

Micky