• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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
0
blossompark
Asked:
blossompark
  • 3
  • 2
2 Solutions
 
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
 
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now