trying to get rid of cursor for update

Question

I need to take the first half of the words in a column and store in another column. I can assume there is an even number of words. I did it using a cursor and a function I found that takes a string and parses it using a delimiter into a table.

drop table #test
create table #test (id int identity, my_name varchar(128), cleaned_name varchar(128))

insert into #test (my_name) VALUES ('abcd efgh abcd1 efgh1')
insert into #test (my_name) VALUES ('name1 name2 name1a name2a')
insert into #test (my_name) VALUES ('one two one* two*')

select *
from #test


DECLARE @HalfName varchar(100)
DECLARE @i varchar(100)
set @i = 1
while @i <= (select count(*) from #test)
begin
      SELECT @HalfName = COALESCE(@HalfName + ' ', '') + aa.WORD
      FROM (select top (select count(*) / 2 from dm_generic.dbo.GETALLWORDS((select [my_name]
      from #test 
      where id = @i), ' ')) *
      from dm_generic.dbo.GETALLWORDS(
      (select [my_name]
      from #test 
      where id = @i), ' ') 
      ) aa

      update #test 
      set cleaned_name = @HalfName 
      where id = @i

      set @i = @i + 1
      set @HalfName = ''
end

select *
from #test

I'm trying to do it without the cursor :

UPDATE bb
   SET cleaned_name =
          (SELECT COALESCE (bb.cleaned_name + ' ', '') + aa.WORD
             FROM (SELECT TOP (SELECT count (*) / 2
                                 FROM dm_generic.dbo.GETALLWORDS (
                                         (SELECT [my_name]
                                            FROM #test a
                                           WHERE a.id = bb.id),
                                         ' '))
                          *
                     FROM dm_generic.dbo.GETALLWORDS ( (SELECT [my_name]
                                                          FROM #test b
                                                         WHERE b.id = bb.id),
                                                      ' ')) aa)
  FROM #test bb

what I'm getting is :

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

any help will be appreciated.

Thanks to all responders, I finally used this solution by @BradC to brew my own, here it is :

update updated
set cleaned_name = (
SELECT Clean
FROM #test AS extern
CROSS APPLY
( 
select TOP (SELECT count (*) / 2 
            FROM dm_generic.dbo.GETALLWORDS (
                                           (SELECT [my_name]
                                            FROM #test a
                                            WHERE a.id = extern.id), ' '))
WORD + ' '
FROM dm_generic.dbo.GETALLWORDS (
         (SELECT [my_name]
            FROM #test a
           WHERE a.id = extern.id),
         ' ')
    FOR XML PATH('')
) pre_trimmed (Clean)
where extern.id = updated.id)
from #test updated

@Nikola Markovinović solution works great as well.

score 1 · Accepted Answer · edited May 23 '17 at 12:27

Local variable concatenation trick does not work without local variable - you are using @HalfName to concatenate words from GETALLWORDS, but it does not work using column name (cleaned_name in this case). To make it work you should use for xml path concatenation trick.

So, if you are working with Sql Server 2005 or newer, try this:

UPDATE bb
   SET cleaned_name =
      (SELECT stuff ((SELECT TOP (SELECT count (*) / 2 
                                   FROM dm_generic.dbo.GETALLWORDS (
                                         (SELECT [my_name]
                                            FROM #test a
                                           WHERE a.id = bb.id),
                                         ' ')
                                  )
                             ' ' + aa.my_name
                        FROM dm_generic.dbo.GETALLWORDS (
                                         (SELECT [my_name]
                                            FROM #test a
                                           WHERE a.id = bb.id),
                                         ' ') aa 
                         FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
                     , 1, 1, '')
      )
  FROM #test bb

Disclaimer: I cannot test this. Try it first with UDF only, then incorporate it into query.

UPDATE: I've misplaced parenthesis - first closing parenthesis in last row should be deleted and one should be appended after last one.

I'm getting "Msg 174, Level 15, State 1, Line 3 The stuff function requires 4 argument(s)." what is the .value and what comes after it in the same line ? — 31 bit, Apr 10 '12 at 12:15
Yes @Nikola Markovinović it works now, though ' ' + aa.my_name should actually be ' ' + aa.WORD which is the name of the column returned from GETALLWORDS. I'm still trying to figure out what is all this : .value('(./text())[1]','VARCHAR(MAX)') — 31 bit, Apr 11 '12 at 07:52
@bodman this part helps with xml special characters. See [this post](http://stackoverflow.com/a/8404473/1231866). — Nikola Markovinović, Apr 11 '12 at 07:58

score 0 · Answer 2 · answered Apr 10 '12 at 11:18

Create below function

ALTER FUNCTION [dbo].[halfWords]
    (
      @InputString VARCHAR(4000)
    )
RETURNS VARCHAR(4000)
AS BEGIN

    DECLARE @Index INT
    DECLARE @Char CHAR(1)
    DECLARE @PrevChar CHAR(1)
    DECLARE @WordCount INT
    DECLARE @WordCount2 INT
    DECLARE @firstHalf varchar(4000)

    SET @Index = 1
    SET @WordCount = 0

    WHILE @Index <= LEN(@InputString)
        BEGIN
            SET @Char = SUBSTRING(@InputString, @Index, 1)
            SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                                 ELSE SUBSTRING(@InputString, @Index - 1, 1)
                            END

            IF @PrevChar = ' '
                AND @Char != ' ' 
                SET @WordCount = @WordCount + 1

            SET @Index = @Index + 1
            SET @wordcount2 = ( @wordcount / 2 )
        END


    SET @wordcount = 0 
    SET @Index = 1
    WHILE @Index <= LEN(@InputString)
        BEGIN
            SET @Char = SUBSTRING(@InputString, @Index, 1)
            SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                                 ELSE SUBSTRING(@InputString, @Index - 1, 1)
                            END

            IF @PrevChar = ' '
                AND @Char != ' ' 
                SET @WordCount = @WordCount + 1


            IF ( @wordcount2 = @WordCount ) 
                BEGIN
                    SET @firstHalf = SUBSTRING(@InputString, 0, @Index) 

                END
            SET @Index = @Index + 1

        END
    RETURN @firstHalf
   END

and then use it in below update query

  UPDATE    #test
  SET       cleaned_name = dbo.[halfWords] (my_name)

Select query for reference

SELECT dbo.halfWords ('one two three four five six seven eight')

will return

one two three four

trying to get rid of cursor for update

2 Answers2