How to split string in 3 pieces in SQL Server

Question

I have the following code which split string in 2 pieces on the basis of space but I want to use this code to split the string in 3 pieces instead of two. is it possible ?

declare @strs nvarchar(max)

set @strs = 'Twinkle Twinkle Little Star, How I wounder what you are.'

set @strs = reverse(@strs)

select reverse(@strs) String,

reverse(right(@strs,len(@strs) - charindex(' ',@strs,30))) Description1,

ltrim(reverse(left(@strs,charindex(' ',@strs,30)))) Description2

Actually I have to copy data from table1 to table2. Table1 has field "companyname" which is varchar(120). But the table2 has 3 fields each is varchar(40)"companyname1", "companyname2","companyname3". I need to split the string in 3 pieces but in a logical way, on the basis of space and with a limit of 40 characters.

E.g if a string is "Alufinish Gesellschaft für Verfahrenstechnik u. zur Metalloberflächenbehandlung mbH & C"

it should split like this on the basis of space

"Alufinish Gesellschaft für" "Verfahrenstechnik u. zur *" "*Metalloberflächenbehandlung mbH & C"

actually I have to copy data from table1 to table2. Table1 has field "companyname" which is varchar(120). But the table2 has 3 fields each is varchar(40)"companyname1", "companyname2","companyname3". So that why I need to split the string in 3 pieces but in a logical way on the basis of space and with a limit of 40 characters. E.g if a string is "LEP Loll Entlackungs- u. Pulverbeschichtungsgesellschaft mbH" it should split like this "LEP Loll Entlackungs-" , "u. Pulverbeschichtungsgesellschaft", "mbH" — Kamran, Oct 24 '13 at 07:44
This is dangerous...Take for instance an example that the initial companyname has a length of exactly 120. Then the resulted strings should be of length exactly 40, or else the insertion would fail. You would have to be lucky to have the space exactly at 41st and 81st characters. — Giannis Paraskevopoulos, Oct 24 '13 at 08:00
@user2906622 Please update your question with additional details. Don't write them in comments. — Nenad Zivkovic, Oct 24 '13 at 08:00
There is some code here to split a string into rows, maybe that helps: http://stackoverflow.com/questions/10852612/split-string-in-sql-server-to-a-maximum-length-returning-each-as-a-row — Spiny Norman, Oct 24 '13 at 08:15
You have spaces in the company name and spaces between company names. How can you know what space to use for the split? — Mikael Eriksson, Oct 24 '13 at 08:51
the space after 39 characters but not split in a not logical manner. — Kamran, Oct 24 '13 at 09:52

score 0 · Accepted Answer · answered Oct 24 '13 at 08:59

A little "brute force" approach.. Perhaps you should encapsulate in a function, and definitely you need to control if there are less than three words in the string, but this should work.

declare @strs nvarchar(max)

set @strs = 'Twinkle Twinkle Little Star, How I wonder what you are. High above noseque sky high'
declare @lon int
declare @palabras int
declare @contador int
declare @posicion int
declare @string1 varchar(max)
declare @string2 varchar(max)
declare @string3 varchar(max)

--wordcount
set @palabras = LEN(@strs)-LEN(replace(@strs, ' ', ''))+1
--words per batch
set @lon = @palabras/3
set @contador = 1
set @posicion = 0
while @contador <= @lon
begin
-- search for the first batch
    set @posicion = CHARINDEX(' ',@strs,@posicion+1)
    set @contador = @contador+1
end 
set @string1 = LEFT(@strs, @posicion)
set @strs = replace(@strs, @string1, '')

set @contador = 1
set @posicion = 0
while @contador <= @lon
begin
-- search for the second batch
    set @posicion = CHARINDEX(' ',@strs,@posicion+1)
    set @contador = @contador+1
end 
set @string2 = LEFT(@strs, @posicion)
set @string3 = replace(@strs, @string2, '')

select @string1, @string2, @string3

How do I control if there are less than 3 words. I have alot of strings with only one word. — Kamran, Oct 24 '13 at 09:38
In the first lines I define the wordcount in *palabras*. If its two you should get only *string1* and *string2* (as *string3* will be empty) if its one then *strs* is your only piece of string — Zelloss, Oct 24 '13 at 09:51

How to split string in 3 pieces in SQL Server

1 Answers1