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
  • 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 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.
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

LVL 12

Accepted Solution

kevin_u earned 500 total points
ID: 24769266
sorry...  substring.
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

LVL 59

Expert Comment

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

Author Closing Comment

ID: 31599434
Well done. Thanks!

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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.…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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