Split data from one filed into two fields

[bmacbmac]

Hello, I have the following data:

create table #temp (field1 varchar(100), field2 (varchar (100))
insert into #temp values ('THIS IS A TEST', NULL)
insert into #temp values ('HAPPY ACRES', NULL)
insert into #temp values ('VANILLA', NULL)

I need to find any entry longer than 10 characters in field1. I need to leave the first 10 characters in field1 and move the remaining characters into field 2. This is simple enough, but what I'm hoping to figure out is to move entire words over (not cutting a word in the middle). So, like this:

Field1                       Field2
THIS IS A                    TEST
HAPPY                        ACRES
VANILLA

I'm not sure how to find the nearest space to 10 characters and then move the the data from there. Can anyone help?

Thanks!

Brian

 

[feherke]

Hi

Ugly brute force :

[b]update[/b] #temp
[b]set[/b] field2 = [b]case[/b] charindex([green][i]' '[/i][/green], reverse(substring(field1, 1, 10)))
[b]when[/b] 0 [b]then[/b] [green][i]''[/i][/green]
[b]else[/b] ltrim(substring(field1, len(substring(field1, 1, 10)) - charindex([green][i]' '[/i][/green], reverse(substring(field1, 1, 10))) + 2, len(field1)))
[b]end[/b];

[b]update[/b] #temp
[b]set[/b] field1 = rtrim(substring(field1, 1, len(field1) - len(field2)));

Note that a first word like "supercalifragilisticexpialidocious" will be kept entirely in field1.

Feherke.
feherke.github.io