DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: using sql statement

  1. #1
    Join Date
    Aug 2000
    Posts
    29
    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
    J.Scott Adams

  2. #2
    Join Date
    Jun 2000
    Posts
    417
    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...

    <font face="courier">
    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;
    </font>

    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]

  3. #3
    Join Date
    Jun 2000
    Location
    Conway,AR,USA
    Posts
    29
    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)


    Soumya
    still learning

  4. #4
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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

  5. #5
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    Forgot to mention,

    you can make a function using my previous post.

    You can use the function in the update statement.

    Regards
    Gert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width