Printable View
I need to populate a column in my database using data from another column using the first character of each word within the column
EXAMPLE:
"old_column"
105 south court street
77 office park road
*brentwood 2
"new_column"
1scs
7opr
*2
Would this be a substr issue?
Any help would be greatly appeciated
thanks,
scotta
Hrm it's hard to do in something like pl/sql, it would be easier in Perl or something but here's the first thing I thought of. This is pseudo code so it's not really going to compile anywhere but you should get the idea...
var = "105 south court street";
-- get the first character
new_column = substr(var, 1, 1)
-- find the first space
space = instr(var, ' ');
while ( space != 0 ) loop
-- space = 4, so we want the 's'
-- this will make new_column = 1s
new_column = new_column || substr(var, space+1, 1)
-- now var = "outh court street"
var = substr(var, space+2);
-- find the next space
space = instr(var, ' ');
end loop;
when the loop is done you'll have the new_column which will hopefully be "1scs". The indexes and things might have to be tweaked a bit but that should give you an idea of how to do it.
after you have the variable you can just do the insert.
[Edited by pwoneill on 12-15-2000 at 01:05 PM]
I have another suggestion.
Step 1. spool the required rows of the column to a file
(make sure each row is one line)(say file 1)
Step 2. Create another table(table1) with the number of columns equal to the maximum number of fields delmited by spaces.
Step 3. load the data from file1 to the new table(table 1).(using SQL*Loader)
Step 4. In the table(table 2) containing new_column
populate it with substr(table1.column1,1,1)||substr(table1.column2,1,1)||....||substr(table1.column n,1,1)
I did a little test and i think we can do what you want by implementing :
declare
work varchar2(100);
spacepos number := 0;
result varchar2(100);
begin
-- remove trailing and leading spaces
work := ltrim(rtrim(' A String With Spaces '));
-- get the first character
result := substr(work,1,1);
-- where is the space ?
spacepos := instr(work,' ');
-- space found, continue
while not spacepos = 0
loop
-- add first char of next word to result
result := result || substr(work,spacepos+1,1);
-- look for next space
spacepos := instr(work,' ',spacepos+1);
end loop;
end;
/
At the end, the variable result contains ASWS .
However, this script assumes you have only one space between the words.
Hope this helps
Gert
Forgot to mention,
you can make a function using my previous post.
You can use the function in the update statement.
Regards
Gert