Solved

sql script required to amend table data

Posted on 2006-07-11
18
251 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tools to scan a SQL server's problem 14 28
SQL Log size 3 20
SQL 2012 clustering 9 13
use of sqlCmd command through exec master..xp_cmdshell 7 16
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

830 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