Tutorial :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??


Solution:1

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  


Solution:2

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.


Solution:3

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...


Solution: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.


Solution:5

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 :)


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »