Solved

sql script required to amend table data

Posted on 2006-07-11
18
221 Views
Last Modified: 2006-11-18
Hi,

I have the following table schema.


column1                                                     column2
hello every body - secondbit - AGR                   NULL
goodbye - secondbit - AGR                                  NULL
great weather - secondbit - AGR                         NULL


I need to populate column2 with whats in column1 between the first and second -

so I would like the table to become..


column1                                                     column2
hello every body - secondbit - AGR                 secondbit
goodbye - secondbit - AGR                         secondbit
great weather - secondbit - AGR                         secondbit


I would also like to remove the remaining stuff after the second -
so the table would finish like so:

column1                       column2
hello every body                 secondbit
goodbye                          secondbit
great weather                    secondbit


many thanks
0
Comment
Question by:jimbona27
  • 7
  • 4
  • 4
  • +1
18 Comments
 
LVL 8

Expert Comment

by:Kobe_Lenjou
Comment Utility
update table set column2 = substring(column1, charindex('-', column1)+1, charindex('-', column1, charindex('-', column1) + 1) - charindex('-', column1)-1 ), column1 = substring ( column1, 1, charindex('-', column1) - 1)
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
Update tableName set Column1 =  left(Column1, charindex('-', Column1)-1),
Column2 = substring(Column1, charindex('-', Column1)+2, (charindex('-', Column1, charindex('-', Column1)+1)) - (charindex('-', Column1)+2))
0
 
LVL 28

Expert Comment

by:imran_fast
Comment Utility


update yourtable set column2 = case when column1 like '%secondbit%'then 'secondbit' else 'whatever' end,
                  column1 = substring (column1, 1,  charindex('-', column1)+1)
0
 
LVL 28

Expert Comment

by:imran_fast
Comment Utility
sorry
update yourtable set column2 = case when column1 like '%secondbit%'then 'secondbit' else 'whatever' end,
               column1 = substring (column1, 1,  charindex('-', column1)-1)
0
 

Author Comment

by:jimbona27
Comment Utility
The 'secondbit' isnt always the same, it was just to illustrate here.  so this wont work "like '%secondbit%'then " or will it?
0
 

Author Comment

by:jimbona27
Comment Utility
using this:
update table1 set column1 = substring(column1 , charindex('-', column1 )+1, charindex('-', column1 , charindex('-', column1 ) + 1) - charindex('-', column1 )-1 ), column1 = substring ( column1 , 1, charindex('-', column1 ) - 1)

I get this:
Invalid length parameter passed to the substring function.
The statement has been terminated.
0
 

Author Comment

by:jimbona27
Comment Utility
i dont mind doing it in several queries if thats easier.
0
 
LVL 8

Expert Comment

by:Kobe_Lenjou
Comment Utility
Possibly there are fields in column1 which do not have the right syntax ( ... - ... - ...) this will cause an error with my query

try this in query analyser:

declare @a varchar(999)
set @a = 'eeeee - ddddd - rrrrr'
select @a, substring(@a, 1, charindex('-', @a)-1), substring(@a, charindex('-', @a)+1, charindex('-', @a, charindex('-', @a) + 1) - charindex('-', @a)-1 )

(just to illustrate that it is working).
This really needs two '-' signs in the column1 field
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 28

Expert Comment

by:imran_fast
Comment Utility
something like this
==============
update yourtable set
 column2 =
            case
            when column1 like '%secondbit%'then 'secondbit'
            when column1 like '%firstbit%'then 'firstbit'
            when column1 like '%thirdbit%'then 'thirdbit'
                  else 'whatever' end,
               

column1 = substring (column1, 1,  charindex('-', column1)-1)
0
 
LVL 8

Accepted Solution

by:
Kobe_Lenjou earned 125 total points
Comment Utility
update table set column2 = substring(column1, charindex('-', column1)+1, charindex('-', column1, charindex('-', column1) + 1) - charindex('-', column1)-1 ), column1 = substring ( column1, 1, charindex('-', column1) - 1) where column1 like '%-%-%'

will only update the ones with the right syntax
0
 

Author Comment

by:jimbona27
Comment Utility
some are 'null' - thats column2
0
 

Author Comment

by:jimbona27
Comment Utility
i've tried Kobe_Lenjou's comment and I get no columns affected.
0
 

Author Comment

by:jimbona27
Comment Utility
imran_fast - how am I', going to define the first and secondbits if the only bit thats constant is the '-' xxx - xxx - xxx
0
 

Author Comment

by:jimbona27
Comment Utility
Kobe_Lenjou's - this seems to work although its not finding anything.
0
 
LVL 8

Expert Comment

by:Kobe_Lenjou
Comment Utility
so a

select count(*) from table where column1 like '%-%-%'

returns 0?
0
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 125 total points
Comment Utility
try this

update yourtable set
 column2 =
          case
          when column1 like '%-%-%'then substring(column1,charindex('-', column1)+1,
            len(column1)-charindex('-', column1)-5 )
                      else 'whatever' end,
               

column1 = substring (column1, 1,  charindex('-', column1)-1)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now