Help with Parsing a String

[MasterPO]

Hi All:

I am trying to write a stored proc that will accept a single parameter as the input and parse this value into multiple 20 character strings. These sub-strings are then written out to a table.

Example: Input string = "I want to be a better T_SQL programmer."

Needs to be broken into 20 char (or less) substrings that do not break in the middle of a word like this:

"I want to be a bette" is 20 char, but needs to be "I want to be a " so the word "better" is not broken.

I will then write this substring out to a table and repeat for the next portion of the input string until there is nothing left.

I tried the following for just the first substring without success:

Declare @strTemp Char(40), @X int
Set @strTemp = Left(@InputString, 40)
Set @X = 40

While Right(@strTemp,1) <> ' '
Begin
Set @strTemp = Left(@InputString, @X)
Set @X = @X - 1
End

Select @strTemp

The idea was to take the first 40 characters, then back up until I found a space and return the resulting string, but it doesn't work.

Any help most appreciated!

 

[ClaireHCM]

I dont really know what you mean.But Try this see whether it works.BTW,could you explain the objective more specifically?
---------------------------------------------------------
Declare @strTemp varchar(200), @X int
declare @inputstring varchar(200)
set @Inputstring = 'I want to be a better T_SQL programmer'
Set @X = 20
set @strtemp = rtrim(ltrim(left(@inputstring,@x)))
--select right(@strtemp,1)
declare @digit varchar(1)
set @digit = right(@strTemp,1)

while @digit <> ' '
Begin
set @strtemp = left(@inputstring,@x)
--select @strtemp
set @digit = right(@strTemp,1)
if @digit <> ' '
begin
set @x = @x-1
end
End
select @strtemp

claire

 

[MasterPO]

That did the trick... I can take care of the rest on my own. Thanks!