• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

SQL - string manipulation and copying - SQL Server 2005

Hello experts,

I have a table with two columns that I would like to move data from one to the other.  The table looks like:

table: mwc_notes_raw
note_id            note_type
1010 0GEN      null
3013 0GEN      null
4013 0INS        null
1919 0INS        null

I want to split apart t he note_id column and copy everything after the first blank space to the note_type column, and remove everything after the space from the first column, and end up with:

table: mwc_notes_raw
note_id            note_type
1010               0GEN
3013               0GEN
4013               0INS
1919               0INS

Something like:

update mwc_notes_raw
set note_type = RIGHT(Notes_primary_Key,charindex(' ',reverse(Notes_primary_Key))-1),
set Notes_primary_Key =LEFT(Notes_primary_Key,charindex(' ',reverse(Notes_primary_Key))-1)

Thoughts?

Thanks!
0
robthomas09
Asked:
robthomas09
2 Solutions
 
knightEknightCommented:
I recommend doing this in two separate update statements -- first set the value of NOTE_TYPE, then correct the value of NODE_ID.
0
 
Ephraim WangoyaCommented:



set  Notes_primary_Key = LEFT(note_id, CHARINDEX(' ', note_id)-1),
       note_type  =  RIGHT(note_id, LEN(note_id) -CHARINDEX(' ', note_id))
0
 
robthomas09Author Commented:
Thanks!
0

Featured Post

Technology Partners: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now