How to invert words in a column

Question

I have a table with varchar(128) column with data like:

word1, word2, , word3, word4
word1, word2, word3, ,
word1,,,, ; word2
;word1 word2, word3

I have to update this table, making words reverse order:

word4, ,word3,,word2 ,word1
,,word3, ,word2, word1

Could you help me to do it using only single sql query??

Answer 1

The best thing to do would be to never store data that way. If this were in a related table as it should be, then you could order to your hearts content. Personally , It would probably be fastest to at least split the data into a temptable or table variable that has an extra identity column and then sort in desc order on that column.

Answer 2

I think you could create a new table and insert into that table the data from the first table.

INSERT INTO TABLE2 SELECT * FROM TABLE1 ORDER BY COLUMN1 DESC

Then, drop table2 and rename table1.

Maybe...

Answer 3

thanks for your answers

words are separated by spaces, commas and/or semicolons, all separators should stay in place

e.g. record

'word1, word2;;;word3, '

evalueates to

'word3, word2;;;word1, '

due to requirements of of existing system it must be done using single query,

i've tried:

update t_desc set name = 
(select name
from
(select name,
case when wb is null then rname when wb >= we then stuff(rname, wb, 128, REVERSE        (substring(rname, wb, 128))) else
stuff(rname, wb, we-wb+1, REVERSE(substring(rname, wb, we-wb+1))) end rname,
case when wb is null or wb > we then null else PATINDEX('%[a-z0-9]%', SUBSTRING(rname, we+1, 128))+we end wb,
case when we is null or wb > we then null else PATINDEX('%[a-z0-9][^a-z0-9]%', SUBSTRING(rname, we+1, 128))+we end we
from
(select name,
case when wb is null then rname when wb >= we then stuff(rname, wb, 128, REVERSE    (substring(rname, wb, 128))) else
stuff(rname, wb, we-wb+1, REVERSE(substring(rname, wb, we-wb+1))) end rname,
case when wb is null or wb > we then null else PATINDEX('%[a-z0-9]%', SUBSTRING    (rname, we+1, 128))+we end wb,
case when we is null or wb > we then null else PATINDEX('%[a-z0-9][^a-z0-9]%',     SUBSTRING(rname, we+1, 128))+we end we
from
(select name,
case when wb is null then rname when wb >= we then stuff(rname, wb, 128, REVERSE        (substring(rname, wb, 128))) else
stuff(rname, wb, we-wb+1, REVERSE(substring(rname, wb, we-wb+1))) end rname,
case when wb is null or wb > we then null else PATINDEX('%[a-z0-9]%', SUBSTRING        (rname, we+1, 128))+we end wb,
case when we is null or wb > we then null else PATINDEX('%[a-z0-9][^a-z0-9]%',         SUBSTRING(rname, we+1, 128))+we end we
from
(select name, 
stuff(rname, wb, we-wb+1, REVERSE(substring(rname, wb, we-wb+1))) rname,
PATINDEX('%[a-z0-9]%', SUBSTRING(rname, we+1, 128))+we wb,
PATINDEX('%[a-z0-9][^a-z0-9]%', SUBSTRING(rname, we+1, 128))+we we 
from
(select name, rname, 
PATINDEX('%[a-z0-9]%', rname) wb,
PATINDEX('%[a-z0-9][^a-z0-9]%', rname) we
  from
(select t_desc.name, REVERSE(name) rname) t1) t2) t3) t4) t5) t6)

and similar ideas but it wasn't work correctly

edit: everything except letters and digits are separators

edit2: unfortunetely i can't invoke ddl statements and i have no direct access to database, my company uses old, closed software - we can only use console of outer application to select or sometimes update. i can add my update to jobs list - it will be performed every day in future.

table has about 60k rows, column contains between 2 and about 20 words, not null

sorry for my english :)

Answer 4

I have a thought since you are constrained to one query. Can you make a table-valued UDF that would do the same thing as the temp table I suggested earlier? Join to that and order desc. It won't be performant (of course nothing will be when the structure is this badly designed) but it might do the job unless you have millions of records.

Or can you make a stored proc and just call that from your GUI, that will allow you to have multiple steps.

Answer 5

To accomplish this task will require a t-sql function and a cursor. fn_SplitList will allow you to split based on a delimiter. Once you have this function you can create a cursor to run against your data updating each record. I created an example using @table1.

Function

CREATE FUNCTION [dbo].[fn_SplitList]
(
    @RowData varchar(8000),
    @SplitOn varchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data varchar(100)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
     Insert Into @RtnValue (data)
     Select 
      Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

     Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
     Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Code to Perform Update

declare @table1 table(id int primary key
                     ,words varchar(max))
declare @id int
declare @words varchar(max)

insert into @table1 values(0, 'word1, word2, , word3, word4')
insert into @table1 values(1, 'word1, word2, word3, ,')
insert into @table1 values(2, 'word1,,,, ; word2')
insert into @table1 values(3, ';word1 word2, word3')

declare updateCursor cursor for
select id
      ,words
  from @table1

open updateCursor   
fetch next from updateCursor into @id, @words
while @@fetch_status = 0
begin
  declare @row varchar(255)

  select @row = coalesce(@row+', ', '') + data
    from dbo.fn_SplitList(@words, ',')
  order by id desc

  update @table1
     set words = @row
   where id = @id

  fetch next from updateCursor into @id, @words
end
close updateCursor   
deallocate updateCursor

select *
  from @table1