Solved

sql script required to amend table data

Posted on 2006-07-11
18
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

623 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