SQL Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Update

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-27 : 10:18:33

Jim writes "I am trying to parse out 2 30 character fields and then update them. The data is orignally coming from another application where it is a 60 character field. The initial update which is out of my control simply chops the 60 character into 2. I need a way to cleanly update these two fields so there are potentially no words split between the 2 fields. I have the logic to cleanly split the fields but no I don't know the proper syntax to perform the update.

Select (LEFT(IMDSC1,(31-Charindex(' ',Reverse(left(imdsc1,30)))))), (RIGHT(IMDSC1,(Charindex(' ',Reverse(left(imdsc1,30))))))+ IMDSC2
from [proddta].F4101 IM
where (Left(IMDSC2,1) <> ' ')

Any ideas!"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-02 : 18:26:25

Something like
select @s2 = right(@s1,charindex(' ',reverse(@s1))) + @s2 ,
@s1 = left(@s1,len(@s1) - charindex(' ',reverse(@s1)))
where charindex(' ',reverse(@s1)) between 1 and 29
and len(@s2) + charindex(' ',reverse(@s1)) <= 30

for columns

update tbl
set s2 = right(s1,charindex(' ',reverse(s1))) + s2 ,
s1 = left(s1,len(s1) - charindex(' ',reverse(s1)))
where charindex(' ',reverse(s1)) between 1 and 29
and len(s2) + charindex(' ',reverse(s1)) <= 30

Been working on that for nerly 3 years.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources