reverse only the text parts of the field

i have to import addresses from an text file.
the text is reversed but numbers are not reversed
"abcd 12 def" comes as "dcba 12 fed"
if i use the reverse function it reveses also the number
and the it becomes "abcd 21 def"
how i can reverse only the text parts of the field?
thanksChop the parts of the string, cehck every single string for ISNUMERIC and
REVERSE it if it evaluates to false, then put the string together in a new
string.
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sam" wrote:

> i have to import addresses from an text file.
> the text is reversed but numbers are not reversed
> "abcd 12 def" comes as "dcba 12 fed"
> if i use the reverse function it reveses also the number
> and the it becomes "abcd 21 def"
> how i can reverse only the text parts of the field?
> thanks
>
>|||how to Chop the parts of the string?
"Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:9044AF5F-E604-4EEC-974C-A2CB06AE5756@.microsoft.com...
> Chop the parts of the string, cehck every single string for ISNUMERIC and
> REVERSE it if it evaluates to false, then put the string together in a new
> string.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Sam" wrote:
>|||Loomk for PATINDEX() in bol
DECLARE @.S Varchar(100)
SET @.s='Some text 123 text'
SELECT PATINDEX('%[0-9]%',@.s)
SELECT LEFT(@.s, PATINDEX('%[0-9]%',@.s)-1)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sam" <focus10@.zahav.net.il> wrote in message
news:eYbeeAamFHA.268@.TK2MSFTNGP10.phx.gbl...
> how to Chop the parts of the string?
> "Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
> wrote in message
> news:9044AF5F-E604-4EEC-974C-A2CB06AE5756@.microsoft.com...
>|||Try this.. should handle all the cases...
/*
ddl
Create table TestRev (Id int, Name Varchar(255))
Insert into TestRev values (1, 'abc 123 def 256')
Insert into TestRev values (2, 'olleh 321 dlrow 987')
Insert into TestRev values (3, 'abc 123456789 reversethis 256')
Insert into TestRev values (4, 'sqlserver 2000 or yukon')
*/
set nocount on
declare @.str Varchar(255)
declare @.id char(15)
declare @.strRev Varchar(255)
declare @.word Varchar(255)
declare @.newWord Varchar(255)
declare @.len int
declare @.pos int
Declare Cur_Rev Cursor
For select id, name from TestRev
open Cur_Rev
Fetch next from Cur_Rev into @.id, @.str
while @.@.fetch_status = 0
begin
set @.str = @.str + ' '
set @.strRev = @.str
set @.len = LEN(@.str)
while (@.len > 1)
begin
set @.pos = PATINDEX('% %',@.str)
set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
if IsNumeric(@.word) <> 1
Begin
set @.newword = reverse(@.word)
set @.strRev = Replace(@.strRev, @.word, @.newWord)
End
set @.str = substring(@.str, @.pos+1, @.len)
set @.len = len(@.str)
if @.pos = 0
break
end
select @.strRev 'Reversed'
fetch next from Cur_Rev into @.id, @.str
set @.strRev = @.str
end
Close Cur_Rev
Deallocate Cur_Rev
go
Thanks,
Prad
"Sam" <focus10@.zahav.net.il> wrote in message
news:eIlxouZmFHA.3648@.TK2MSFTNGP10.phx.gbl...
>i have to import addresses from an text file.
> the text is reversed but numbers are not reversed
> "abcd 12 def" comes as "dcba 12 fed"
> if i use the reverse function it reveses also the number
> and the it becomes "abcd 21 def"
> how i can reverse only the text parts of the field?
> thanks
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:544016
Pradeep Kutty wrote:
> Try this.. should handle all the cases...
> /*
> ddl
> Create table TestRev (Id int, Name Varchar(255))
> Insert into TestRev values (1, 'abc 123 def 256')
> Insert into TestRev values (2, 'olleh 321 dlrow 987')
> Insert into TestRev values (3, 'abc 123456789 reversethis 256')
> Insert into TestRev values (4, 'sqlserver 2000 or yukon')
> */
>
> set nocount on
> declare @.str Varchar(255)
> declare @.id char(15)
> declare @.strRev Varchar(255)
> declare @.word Varchar(255)
> declare @.newWord Varchar(255)
> declare @.len int
> declare @.pos int
>
> Declare Cur_Rev Cursor
> For select id, name from TestRev
>
> open Cur_Rev
> Fetch next from Cur_Rev into @.id, @.str
> while @.@.fetch_status = 0
> begin
> set @.str = @.str + ' '
> set @.strRev = @.str
> set @.len = LEN(@.str)
> while (@.len > 1)
> begin
> set @.pos = PATINDEX('% %',@.str)
> set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
> if IsNumeric(@.word) <> 1
> Begin
> set @.newword = reverse(@.word)
> set @.strRev = Replace(@.strRev, @.word, @.newWord)
> End
> set @.str = substring(@.str, @.pos+1, @.len)
> set @.len = len(@.str)
> if @.pos = 0
> break
> end
> select @.strRev 'Reversed'
> fetch next from Cur_Rev into @.id, @.str
> set @.strRev = @.str
> end
> Close Cur_Rev
> Deallocate Cur_Rev
> go
>
> Thanks,
> Prad
>
Doesn't work for if the string contains a string and it's reverse:
insert into TestRev values (5,'abc cba abc cba')
and run again (I get) 'abc abc abc abc'
I cannot work out how to fix that (other than to build up StrRev one
word at a time rather than doing replaces.
Damien|||yes what you said is correct:
heres a fix for that.. this should work try...
set nocount on
declare @.str Varchar(255)
declare @.id char(15)
declare @.strRev Varchar(255)
declare @.word Varchar(255)
declare @.newWord Varchar(255)
declare @.len int
declare @.pos int
Declare Cur_Rev Cursor
For select id, name from TestRev
open Cur_Rev
Fetch next from Cur_Rev into @.id, @.str
while @.@.fetch_status = 0
begin
set @.str = @.str + ' '
set @.strRev = ''
set @.len = LEN(@.str)
while (@.len > 1)
begin
set @.pos = PATINDEX('% %',@.str)
set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
if IsNumeric(@.word) <> 1
Begin
Select @.newword = reverse(@.word)
set @.strRev = @.strRev + ' ' + @.newWord
End
if IsNumeric(@.word) = 1
set @.strRev = @.strRev + ' ' + @.word
set @.str = substring(@.str, @.pos+1, @.len)
set @.len = len(@.str)
if @.pos = 0
break
end
select ltrim(rtrim(@.strRev)) 'Reversed'
fetch next from Cur_Rev into @.id, @.str
set @.strRev = @.str
end
Close Cur_Rev
Deallocate Cur_Rev
go
Thank,
Prad
"Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
> Pradeep Kutty wrote:
> Doesn't work for if the string contains a string and it's reverse:
> insert into TestRev values (5,'abc cba abc cba')
> and run again (I get) 'abc abc abc abc'
> I cannot work out how to fix that (other than to build up StrRev one
> word at a time rather than doing replaces.
> Damien
>|||If you have a value seperator (delimiter) you could use this.
e.g
-- digits table needs to be created once
select top 8000 digit=identity(int,1,1)
into digits
from sysobjects,syscolumns
go
create function dbo.xreverse(@.input varchar(8000))
returns varchar(8000)
as
begin
declare @.tb table (i int identity primary key, value sysname)
declare @.s varchar(8000)
declare @.delim char
set @.delim=space(1)
set @.input = @.delim+rtrim(ltrim(@.input))+@.delim
insert @.tb
select case when isnumeric(value)=0 then reverse(value) else value end
from (
select substring(@.input, n.digit+1,
charindex(@.delim,@.input,n.digit+1)-n.digit-1) value
from digits as n
where n.digit<len(@.input)
and substring(@.input,n.digit,1) = @.delim
)x
select @.s=isnull(@.s+@.delim,'')+value from @.tb
return @.s
end
go
select dbo.xreverse(Name)
from TestRev
-oj
"Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
> Pradeep Kutty wrote:
> Doesn't work for if the string contains a string and it's reverse:
> insert into TestRev values (5,'abc cba abc cba')
> and run again (I get) 'abc abc abc abc'
> I cannot work out how to fix that (other than to build up StrRev one
> word at a time rather than doing replaces.
> Damien
>|||in most cases it's work nice
but in cases like "cba (2000) fed" or "cba 2/3 fed" or "cba 2-3 fed"
it's wrong
sam
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message
news:elyhOYdmFHA.3828@.TK2MSFTNGP12.phx.gbl...
> yes what you said is correct:
> heres a fix for that.. this should work try...
>
> set nocount on
> declare @.str Varchar(255)
> declare @.id char(15)
> declare @.strRev Varchar(255)
> declare @.word Varchar(255)
> declare @.newWord Varchar(255)
> declare @.len int
> declare @.pos int
>
> Declare Cur_Rev Cursor
> For select id, name from TestRev
>
> open Cur_Rev
> Fetch next from Cur_Rev into @.id, @.str
> while @.@.fetch_status = 0
> begin
> set @.str = @.str + ' '
> set @.strRev = ''
> set @.len = LEN(@.str)
> while (@.len > 1)
> begin
> set @.pos = PATINDEX('% %',@.str)
> set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
> if IsNumeric(@.word) <> 1
> Begin
> Select @.newword = reverse(@.word)
> set @.strRev = @.strRev + ' ' + @.newWord
> End
> if IsNumeric(@.word) = 1
> set @.strRev = @.strRev + ' ' + @.word
> set @.str = substring(@.str, @.pos+1, @.len)
> set @.len = len(@.str)
> if @.pos = 0
> break
> end
> select ltrim(rtrim(@.strRev)) 'Reversed'
> fetch next from Cur_Rev into @.id, @.str
> set @.strRev = @.str
> end
> Close Cur_Rev
> Deallocate Cur_Rev
> go
> Thank,
> Prad
> "Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
> news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
>