Reversing variable sized column data in a sql server 2005 table column

Hi,
     I have a column in a table of approx 10,000 rows which currently displays typically as shown below

Office/Users/Partners
Safety Users/LegacyOffice/Users/Partners
ECG /linen/ LegacyOffice/Users/Partners


I want to update the column to display as below

Partners/Users/Office
Partners/Users/LegacyOffice/Users/SafetyUsers
Partners/Users/LegacyOffice/linen/ECG

The question has been previously  addressed by sdstuber in this question
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27411842.html
The difference here is that the number of ‘elements’ in each column field may vary…
Any guidance appreciated…thanks
blossomparkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachitjainCommented:
This is your function that could do this for you

CREATE FUNCTION ReverseString
(
      -- Add the parameters for the function here
      @str varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
      declare @str2 as varchar(max)
      set @str2 = ''

      declare @i as integer, @len as integer
      set @len = LEN(@str)
      set @i = 1
      while @i>0
      begin
            set @i = CHARINDEX('/', @str, @i)
            if @i >= 1
            begin
                  if LEN(@str2) = 0
                        set @str2 = SUBSTRING(@str, 1, @i-1)
                  else
                        set @str2 =  SUBSTRING(@str, 1, @i-1) + '/' + @str2
                  set @str = SUBSTRING(@str, @i + 1, @len-@i)
                  set @len = LEN(@str)
            end
            else
            begin
                  if LEN(@str2) = 0
                        set @str2 = @str
                  else
                        set @str2 =  @str + '/' + @str2
                  break
            end
      end
      return @str2
END
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sachitjainCommented:
You just need to call it like this

update <tableName> set <ColName>=dbo.ReverseString(<ColName>)

I tested it over following sample code and succeeded
declare @str as varchar(100), @str2 as varchar(max)
set @str = N'Str1/Str2/Str3/Str4/Str6'
select @str2 = dbo.ReverseString(@str)
print @str2
0
blossomparkAuthor Commented:
Hi sachitjain:
thanks for your response...will implement and update you with results
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

LowfatspreadCommented:
try this...

i believe that sachijan's solution has an error ....

 set @i = CHARINDEX('/', @str, @i)

should be

 set @i = CHARINDEX('/', @str)

otherwise it doen't seem to work when the terms vary in length....


my version adapted from sachijans uses Stuff to grow the result string
and tries to minimise the actual code and string manipulation involved.


--drop FUNCTION ReverseString2
drop function dbo.reversestring_2
go
CREATE FUNCTION dbo.ReverseString_2
(
      -- change abc/def/ghi  to ghi/def/abc
 /*
      take input string and reverse the order the terms delimited by the delimiter
      1. initialise output string to a space
      2. search for delimiter
      3. when found 
          i STUFF the output string by overwriting the first character
               with space + delimiter + the term found
         ii increment the start position in the input string to search
        iii loop
      4. when not found 
            stuff the output string by overwriting the first character
               with space + delimiter + the remaining characters
      5. return the output string stripping the first len(' '+@delimmiter) characters
            
 test cases
select dbo.reversestring_2('abc/def/ghi/jklmnop/q/rstu/vw/xyz','/')
select dbo.reversestring_2('abc/def/ghi','/')
select dbo.reversestring_2('abcdef','/')
select dbo.reversestring_2('abc//ghi/jklmnop/q/rstu/vw/xyz','/')
select dbo.reversestring_2('abc/def/','/')
select dbo.reversestring_2('abc/def/ghi/jklmnop/q/rstu/vw/xyz','K')
select dbo.reversestring_2(NULL,'.')
*/
      @str varchar(max)
     ,@delimiter varchar(max) = '/'
     
)
RETURNS varchar(max)
AS
BEGIN
/*
declare @str varchar(max) = 'Abc/def/ghi'
declare @delimiter varchar(max) = '/'
*/
      declare @str2 as varchar(max)=' '
      declare @i as integer = 1,@pos integer = 1
      
      while @i>0
      begin
            set @i = CHARINDEX(@delimiter, @str , @pos)
 /*
            print @str
            print @str2
            print @i
            print @pos
            print '----'     
 */
            select @str2 =stuff(@str2,1,1,
                                ' '+@delimiter
                                +substring(@str,@pos
                                            ,case when @i>0 then @i else LEN(@str)+1 end -@pos ))
                 ,@pos=@i+LEN(@delimiter)  
      end
 -- print left(stuff(@str2,1,len(@delimiter)+1,''),len(@str2)-len(@delimiter)-1)
 return left(stuff(@str2,1,len(@delimiter)+1,''),len(@str2)-len(@delimiter)-1)
     
END

go
select dbo.reversestring_2('abc/def/ghi/jklmnop/q/rstu/vw/xyz','/')
select dbo.reversestring_2('abc/def/ghi','/')
select dbo.reversestring_2('abcdef','/')
select dbo.reversestring_2('abc//ghi/jklmnop/q/rstu/vw/xyz','/')
select dbo.reversestring_2('abc/def/','/')
select dbo.reversestring_2('abc/def/ghi/jklmnop/q/rstu/vw/xyz','K')
select dbo.reversestring_2(NULL,'.')

Open in new window

0
blossomparkAuthor Commented:
Hi  Lowfatspread,
thanks for the input...
yes sachitjain solution is returning unexpected results....
will implement yours and update later
0
blossomparkAuthor Commented:
Hi Lowfatspread,
                               I altered sachitjain's code as per your comment and it works fine now...i havent implemented your "stuff" code as sachitjain's is working fine...
thank you both for your help....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.