split existing field

[kumori25]

does anyone have any idea on how i can split data from an existing table? i.e. existing table has a field of NAMES, which includes salutations. i need to be able to split this single field into 2 fields and port all the data into a new table.
e.g.
existing data:
NAMES field : ms joy

new data:
SALUTATION field: ms
NAMES field: joy

there are about 4000 records in the existing table. anyone have any idea how to run through all the existing data, split it and then transfer to a new table?

 

[ManjulaMadhu]

How do you plan to do it? SQL or any other tool ?

 

[sem]

insert into new_table (salutation, name)
select
substr(field, 1, instr(field,' ') - 1) salutation,
substr(field, instr(field,' ') + 1) name
from old_table


Regards, Dima

 

[kumori25]

thanks guys,

any idea if Dima's codes can be automated, using toad or any other sql tool? i've got lots of data and the tables consists of many fields

 

[kumori25]

hi again,

i tried dima's codes, but it's not working. can anyone explain what
"substr(field, 1, instr(field,' ') - 1)" means?
i.e. what exactly does the function instr(field,'') do?

thanks

 

[jee]

substr is the substring function - with 3 parameters -
1) input string
2) starting position (1 - first character)
3) Number of characters in the substring to return - if omitted, means return to the end of the input string

Returned is the substring

instr searches a string for an occurance of a specified character and returns the position. Parameters are:
1) Input string
2) Character for which to search
3) there are a couple of optional ending parameters; Their absence means to search string from left-to-right and stop of the first occurrence of the character.

Returns the number position of the character (or zero if not found).

So what this should do is get you the substring from the start of your input string, up to but not including the first space.

 

[PremKrishnan]

Regarding the split up of a single field in two
first all the records in the table should be separated
by ' ' delimiter and there should not be more than one space between characters.

Secondly if the above condition is satisfied then we have to write a cursor and fetch each and every record and populate in the new table.
This can be done using Dima's logic...

Prem