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?
UOCAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kevin_uConnect With a Mentor Commented:
sorry...  substring.
0
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

From novice to tech pro — start learning today.