reverse string using SQL function

Alpesh Loriya
Alpesh Loriya used Ask the Experts™
on
Hi,

I want the function that reverse my string...
Example:
/12/13/14/
that should return
 /14/13/12/

i have to just pass the string
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
hey i want the same function found for Oracle

http://www.experts-exchange.com/Database/Oracle/Q_22052031.html?sfQueryTermInfo=1+revers+sql+string+us#a17891515

i want that for MS SQL 2000
SELECT REVERSE('string')
Sorry, reverse() only reverse the string.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
REVERSE function gives wrong result for double digits
Commented:
In the code attached you've got your function. Once you've created it you call it like that

select dbo.ReverseNumbers('/12/13/14/')



create function ReverseNumbers (@str varchar(max))
returns varchar(max)
as
begin
	declare @output varchar(max)
	set @output = ''

	declare @i int

	set @i = charindex('/',@str)
	while @i>0 
	begin
		set @output = '/'+left(@str,@i-1) + @output
		set @str = stuff(@str,1,@i,'')
		set @i = charindex('/',@str)
	end
	set @output = @str + @output

	return @output
end
go

Open in new window

SharathData Engineer

Commented:
Here you go.
declare @string varchar(2000)
set @string = '/12/13/14/'

declare @output varchar(2000)

select @output = COALESCE(@output+'/' ,'')+string_1 
  from (SELECT string,n,ltrim(SUBSTRING(string, n, CHARINDEX('/', string + '/',n) - n)) AS string_1
          FROM (select @string string) as t1
         CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
         WHERE SUBSTRING('/' + string, n, 1) = '/'
           AND n < LEN(string) + 1) as t1 
 where string_1 <> '' order by n desc

select '/'+@output+'/' as Reverse_String

-- /14/13/12/

Open in new window

SharathData Engineer
Commented:
You can create the function like this and call in your query.

select Your_Column,dbo.Reverse_Fun(Your_Column) as Reverse_Column
  from Your_Table
create function reverse_fun(@string varchar(2000)) 
returns varchar(2000) as 
begin
declare @output varchar(2000)

select @output = COALESCE(@output+'/' ,'')+string_1 
  from (SELECT string,n,ltrim(SUBSTRING(string, n, CHARINDEX('/', string + '/',n) - n)) AS string_1
          FROM (select @string string) as t1
         CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
         WHERE SUBSTRING('/' + string, n, 1) = '/'
           AND n < LEN(string) + 1) as t1 
 where string_1 <> '' order by n desc

return '/'+@output+'/' 
end

Open in new window

Author

Commented:
Many thanks guys, it is really helpful to work with your system
SharathData Engineer

Commented:
lof has used a WHILE loop, you can avoid that with my query.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial