Split data from one field into two fields

Posted on 2009-07-02
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 -
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?
Question by:UOC
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
  • 3
  • 2
  • 2
LVL 12

Expert Comment

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.

Author Comment

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

I am using SQL7
LVL 60

Expert Comment

by:Kevin Cross
ID: 24769243
Try with substring(str, i, n) where i is starting index and n is count of chars.
A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

LVL 12

Accepted Solution

kevin_u earned 500 total points
ID: 24769266
sorry...  substring.
LVL 60

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

LVL 60

Expert Comment

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

Author Closing Comment

ID: 31599434
Well done. Thanks!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

632 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