Solved

Split data from one field into two fields

Posted on 2009-07-02
7
455 Views
Last Modified: 2012-05-07
I have a field 'called patno_samno' in an SQL 7 table called 'anal_06' which has data in the following format -
218469-001
I want to put the first part (218469) into another field called 'patno1' and the second part (001) into a field called 'samno1'. There are thousands of these entries. The first part (the patno) all have 6 characters and the second (the samno) all have 3 characters and both are seperated by a dash in the 'patno_samno' field.

How do I copy the required data to seperate fields please?
0
Comment
Question by:UOC
  • 3
  • 2
  • 2
7 Comments
 
LVL 12

Expert Comment

by:kevin_u
ID: 24769152
create the new fields in the database

update anal_06 set patno1 = substr(patno_samno,1,6), samno1 = substr(patno_samno, 8, 3)

you can add a where clause to do it on subsets of data.
0
 

Author Comment

by:UOC
ID: 24769204
'substr' is not a recognized function name.

I am using SQL7
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24769243
Try with substring(str, i, n) where i is starting index and n is count of chars.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 12

Accepted Solution

by:
kevin_u earned 500 total points
ID: 24769266
sorry...  substring.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24769274
If this is variable length and you need to split on hyphen ('-') then just do like this:
update anal_06

set patno1 = left(patno_samno, charindex('-', patno_samno)-1)

, samno1 = right(patno_samno, charindex('-', reverse(patno_samno))-1)
 

-- or with fixed widths, simply

update anal_06

set patno1 = left(patno_samno, 6)

, samno1 = right(patno_samno, 3);

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24769276
@kevin_u, welcome to EE!
0
 

Author Closing Comment

by:UOC
ID: 31599434
Well done. Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

948 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now