Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

sql script required to amend table data

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
jimbona27
Asked:
jimbona27
  • 7
  • 4
  • 4
  • +1
2 Solutions
 
Kobe_LenjouCommented:
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
 
imrancsCommented:
Update tableName set Column1 =  left(Column1, charindex('-', Column1)-1),
Column2 = substring(Column1, charindex('-', Column1)+2, (charindex('-', Column1, charindex('-', Column1)+1)) - (charindex('-', Column1)+2))
0
 
imran_fastCommented:


update yourtable set column2 = case when column1 like '%secondbit%'then 'secondbit' else 'whatever' end,
                  column1 = substring (column1, 1,  charindex('-', column1)+1)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
imran_fastCommented:
sorry
update yourtable set column2 = case when column1 like '%secondbit%'then 'secondbit' else 'whatever' end,
               column1 = substring (column1, 1,  charindex('-', column1)-1)
0
 
jimbona27Author Commented:
The 'secondbit' isnt always the same, it was just to illustrate here.  so this wont work "like '%secondbit%'then " or will it?
0
 
jimbona27Author Commented:
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
 
jimbona27Author Commented:
i dont mind doing it in several queries if thats easier.
0
 
Kobe_LenjouCommented:
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
 
imran_fastCommented:
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
 
Kobe_LenjouCommented:
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
 
jimbona27Author Commented:
some are 'null' - thats column2
0
 
jimbona27Author Commented:
i've tried Kobe_Lenjou's comment and I get no columns affected.
0
 
jimbona27Author Commented:
imran_fast - how am I', going to define the first and secondbits if the only bit thats constant is the '-' xxx - xxx - xxx
0
 
jimbona27Author Commented:
Kobe_Lenjou's - this seems to work although its not finding anything.
0
 
Kobe_LenjouCommented:
so a

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

returns 0?
0
 
imran_fastCommented:
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 7
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now