February 25, 2004 at 2:46 pm
bug fix.
create function fn_reverse (@strin varchar(4000))
returns varchar(4000)
as
begin
declare @return varchar(4000)
declare @ascii smallint, @numeric bit, @space smallint, @start smallint, @garbage varchar(30)
declare @i smallint, @j-2 smallint
select @i = 1, @start = 1, @garbage = '{}][\/"'';_+,'
--clean up
while @i <= len(@garbage)
begin
set @strin = replace(@strin, substring(@garbage,@i,1),'')
set @i = @i + 1
end
set @i = 1
while @i <= len(@strin)
begin
set @ascii = ascii(substring(@strin, @i, 1))
if @ascii >= 48 and @ascii <= 57 --number
begin
if @space is not null
begin
set @return = isnull(@return,'') + reverse(substring(@strin, @start, (@space - @start)))+ ' '
set @start = @space + 1
set @space = null
set @i = @start
end
else
begin
set @j-2 = CHARINDEX(' ',@strin, @i)
if @j-2 = 0 --end of string
set @j-2 = len(@strin) + 1
set @return = isnull(@return,'') + substring(@strin, @start, (@j - @start))+ ' '
set @start = @j-2 + 1
set @i = @start
end
end
else
begin
if @ascii = 32 --space
set @space = @i
set @i = @i + 1
end
end
return rtrim(@return)
end
February 27, 2004 at 5:43 am
hi
i dont understanding what appen
when i put the function on this
------------------
declare @strin varchar(1000)
set @strin = 'noitcennoc tenr[et]ni na htiw 1234-ABCD "g\nihtyna +,,tuoba HIJK-5678'
select dbo.fn_reverse(@strin)
--------------------------------
it work ok
but when i do this
----------------------
SELECT TAGNAME, dbo.fn_reverse(TAGDESC)as dd,TAGDESC
FROM tbtxt
order by dd desc
----------------------------
then the
Caps are REVERSE !!!
-------------------------------------
most of my text is on Hebrew and it mix with CAPS on English
perhaps it explain the problemn !!!
so the problem right now is only with the CAPS on English
the Hebrew is ok and the numbers is ok
thnks for all
ilan
February 29, 2004 at 3:52 pm
Can you post a sample with Caps which should be reserved?
March 8, 2004 at 2:54 am
How can I do this???
I wont to use this function like this!!!
Loop every single record and insert it to a new table
I wont to check every record one after one
So that the function check one record and put it in table
So the I gets all the 800000 records
In a new table after it check it one by one
thnks
March 8, 2004 at 3:07 am
If the function is working with a predefined string but not from your table then the problem must be with the data not conforming to the rules of the function.
As wz700 has asked, post the row that is causing the problem. Only by seeing the exact text can we see what the problem is.
Far away is close at hand in the images of elsewhere.
Anon.
March 8, 2004 at 5:52 am
i found the problem !!!!!
it is in the function !!!!
when i do this
-----------------------
declare @strin varchar(5000)
set @strin = 'xxxxxxxxxxxxxxxxx'
select dbo.fn_reverse(@strin)
------------------------------
i get NULL value
OR THIS
--------------------------
declare @strin varchar(5000)
set @strin = ' 50 aa bb cc '
select dbo.fn_reverse(@strin)
------------------------------
i get only the value=50
can you fix it ??
thnks ilan
March 8, 2004 at 7:21 am
alter function fn_reverse (@strin varchar(4000))
returns varchar(4000)
as
begin
declare @return varchar(4000)
declare @ascii smallint, @numeric bit, @space smallint, @start smallint, @garbage varchar(30)
declare @i smallint, @j-2 smallint
select @i = 1, @start = 1, @garbage = '{}][\/"'';_+,'
--clean up
while @i <= len(@garbage)
begin
set @strin = replace(@strin, substring(@garbage,@i,1),'')
set @i = @i + 1
end
set @strin = ltrim(@strin)
set @i = 1
while @i <= len(@strin)
begin
set @ascii = ascii(substring(@strin, @i, 1))
if @ascii >= 48 and @ascii <= 57 --number
begin
if @space is not null
begin
set @return = isnull(@return,'') + reverse(substring(@strin, @start, (@space - @start)))+ ' '
set @start = @space + 1
set @space = null
set @i = @start
end
else
begin
set @space = CHARINDEX(' ',@strin, @i)
if @space = 0 --end of string
set @space = len(@strin) + 1
set @return = isnull(@return,'') + substring(@strin, @start, (@space - @start))+ ' '
set @start = @space + 1
set @space = null
set @i = @start
end
end
else
begin
if @ascii = 32 --space
set @space = @i
set @i = @i + 1
end
end
if @start != @i
set @return = isnull(@return,'') + reverse(substring(@strin, @start, (@i - @start)))
return rtrim(@return)
end
go
Test cases
declare @strin varchar(5000)
set @strin = 'axxxxxxxxxxxxxxxxx'
select dbo.fn_reverse(@strin)
xxxxxxxxxxxxxxxxxa
declare @strin varchar(5000)
set @strin = ' 50 aa bb cc '
select dbo.fn_reverse(@strin)
50 cc bb aa
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply