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

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

Split data from one field into two fields

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
UOC
Asked:
UOC
  • 3
  • 2
  • 2
1 Solution
 
kevin_uCommented:
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
 
UOCAuthor Commented:
'substr' is not a recognized function name.

I am using SQL7
0
 
Kevin CrossChief Technology OfficerCommented:
Try with substring(str, i, n) where i is starting index and n is count of chars.
0
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.

 
kevin_uCommented:
sorry...  substring.
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
@kevin_u, welcome to EE!
0
 
UOCAuthor Commented:
Well done. Thanks!
0

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.

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