Solved

sql script required to amend table data

Posted on 2006-07-11
18
242 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
ID: 17080546
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
ID: 17080562
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
ID: 17080572


update yourtable set column2 = case when column1 like '%secondbit%'then 'secondbit' else 'whatever' end,
                  column1 = substring (column1, 1,  charindex('-', column1)+1)
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 28

Expert Comment

by:imran_fast
ID: 17080577
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
ID: 17080773
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
ID: 17080785
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
ID: 17080858
i dont mind doing it in several queries if thats easier.
0
 
LVL 8

Expert Comment

by:Kobe_Lenjou
ID: 17080944
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 17080957
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
ID: 17080961
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
ID: 17080965
some are 'null' - thats column2
0
 

Author Comment

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

Author Comment

by:jimbona27
ID: 17080992
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
ID: 17081014
Kobe_Lenjou's - this seems to work although its not finding anything.
0
 
LVL 8

Expert Comment

by:Kobe_Lenjou
ID: 17081415
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
ID: 17081520
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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