help with the " LTRIM " and split
-
need help with solution after import data into sql table
i have problem with one field !!
- this field reversed
- it have character thet contain
number
letter (of alphabet)
and letter (of Caps)
so if i use the command "REVERSE "
all the field will bee reversed included the
Numbers
my question is
how can i revers only the
letter (of alphabet) ????
and not the
number ,and not the letter (with Caps)
i need to split the sentence to "
letter (of alphabet) and letter (of Caps) and
number
and then "reversed" only the letter (of alphabet)
thnks ilan
-
Can you post some examples? -- of what you have and what you want --
* Noel -
like this
"noitcennoc tenretni na htiw 1234-ABCD gnihtyna tuoba HIJK-5678"
i need to revers all and
not the "1234-ABCD " AND NOT THE "HIJK-5678"
so after i revers it i get this !!!
"With an Internet connection 1234-ABCD about anything HIJK-5678"
thnks
ilan
-
Bit of a quick hack!
Use split function from http://www.sqlservercentral.com/scripts/contributions/850.asp
Declare @Answ varchar(8000)
Set @Answ=''
Select @Answ=@Answ+strval+' '
From (Select Case When Not strval like '%[0-9]%'
And Cast(Lower(strVal) as varbinary(8000))=Cast(strVal as varbinary(8000))
Then Reverse(strval)
Else strval End
from dbo.split('noitcennoc aAbb cba tenretni na htiw 1234-ABCD gnihtyna tuoba HIJK-5678',' ')) as f(strVal)
Select @Answ
-------------------------------------------------------------------------
connection aAbb abc internet an with 1234-ABCD anything about HIJK-5678
(1 row(s) affected)
-
thnks
problem 1
the code get error
----------------------------------------
Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.split'.
---------------------------------------------------
problem 2
how can i loop this sql code in this field
i get in the table 80000 records !!!!!
-
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
declare @i smallint, @j-2 smallint
select @i = 1, @start = 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
go
To test it.
declare @strin varchar(1000)
set @strin = 'noitcennoc tenretni na htiw 1234-ABCD gnihtyna tuoba HIJK-5678'
select dbo.fn_reverse(@strin)
result:
with an internet connection 1234-ABCD about anything HIJK-5678
-
WOW genius brilliant
ok
need help
problem 1
how can i loop the select
---------------------------------
SELECT TAGNAME, TAGDESC, EUDESC
FROM tbtxt
-----------------------------------------
my problem with the field "TAGDESC" this field is the "REVERSE "
how can i loop the function in this field
and then use select ????
problem 2
it not work ok where i get words like this !!! (WITH " AND ')
------------------
'noitcennoc "tenr "etni" na htiw 1234-ABCD gn i ' htyna tuo ' ba ' HIJK-5678'
------------------------------------------------
THE PROBLEM IS TO KEEP the character [" " ,' ',] 
( BETWEEN THE WORDS
or to clean all the (garbage) character [" " ,' ',] 
( BETWEEN the words !!!thnks ilan
-
SELECT TAGNAME,
REVERSE(LEFT(TAGDESC,PATINDEX('%[0-9]%',TAGDESC)-2)) +
SUBSTRING(TAGDESC,PATINDEX('%[0-9]%',TAGDESC)-1,CHARINDEX(' ',TAGDESC,PATINDEX('%[0-9]%',TAGDESC)-PATINDEX('%[0-9]%',TAGDESC))) +
REVERSE(SUBSTRING(TAGDESC,CHARINDEX(' ',TAGDESC,PATINDEX('%[0-9]%',TAGDESC))+1,LEN(TAGDESC)-CHARINDEX(' ',REVERSE(TAGDESC))-CHARINDEX(' ',TAGDESC,PATINDEX('%[0-9]%',TAGDESC)))) +
RIGHT(TAGDESC,CHARINDEX(' ',REVERSE(TAGDESC))) AS 'TAGDESC',
EUDESC
FROM tbtxt
Far away is close at hand in the images of elsewhere.
Anon. -
thnks but i get a error
-----------------
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function
----------------------------------
did it
clean all the (garbage) and the character [" " ,' ',] ( BETWEEN the words ?????
-
You can use the function directly in query.
SELECT TAGNAME, dbo.fn_reverse(TAGDESC), EUDESC
FROM tbtxt
Not sure about the second one?
-
thnks men
it work but
if between the words i have this
)( , \; ' / \ .
it cut it and i get only part of the words
so how can i clean
all the (garbage) before i run the
function
thnks ilan
-
I would prefer to cut them before calling the function for two reasons.
1. the list can be changing.
2. outside the reverse logic.
You may want to use REPLACE (or combine with CHARINDEX) if doing from SQL.
-
can you hlep last time ?
for clean Strip the field before i run the function
and remove all this
the (garbage) from the field !!
-----------------------
this Symbols
{}][\ / '' ; - _+ - ,
----------------------------
I FOUND THIS CODE IT IS FOR "VBSCRIPT" FOR Strip Symbols IN ASP PAGE !
maybe it good for it ?????????
for conver it for SQL SERVER
---------------------------
Function StripSymbols(sString)
Dim nCharPos, sOut, nChar
nCharPos = 1
sOut = ""
For nCharPos = 1 To Len(sString)
nChar = Asc(Lcase(Mid(sString, nCharPos, 1)))
If ((nChar > 47 And nChar < 58) or_
(nChar > 96 And nChar < 123) or_
nChar = 32) Then
sOut = sOut & Mid(sString, nCharPos, 1)
End If
Next
StripSymbols = sOut
End Function
---------------------------------
thnks for your help
ilan
-
Garbage clean up inside the function:
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
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
go
For character(s) not needed, include it in @garbage string. No space between, otherwise space itself will be cleaned up.
Sample:
declare @strin varchar(1000)
set @strin = 'noitcennoc tenr[et]ni na htiw 1234-ABCD "g\nihtyna +,,tuoba HIJK-5678'
select dbo.fn_reverse(@strin)
result:
with an internet connection 1234-ABCD about anything HIJK-5678
-
WOW it work ok
thnks thnks thnks ..............
----------
litele problem
the the Caps are REVERSE can you fix it ???
thnks for all
ilan
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply