Syntax for using Trim, Ltrim, and Rtrim & sample of output

I don't understand your response. I already created a control file and loaded the data into an oracle table. I am trying to remove whitespace and the numbers.

Well ok, then use substr to remove numbers and whitespace. If you post you query results for :SELECT * FROM your_table; we are much easily understand your problem.

sikyala,
   You need to do a little parsing, but it's pretty easy:

update yourtable
   set col1 = substr(col1, instr(col1,' ')+1, instr(col1, ' ', 1, 2) - instr(col1, ' ')),  -- this looks for the first space, then grabs everything from there to the next space
        col2 = substr(col1, instr(col1,' ')+1),  -- grab everything after the first space
        col3= <same as #2>

That should do it.

Good luck!
DrSQL

For case 3 a sample of the data in ACCENS_NUM field (Primary Key of the table) is as follows:

(1)     NT-53575     NT53575
(1)     NT-53574     NT53574
(1)     NT-53573     NT53573
(1)     NT-53572     NT53572

- I want to essentially delete (1)
- delete the white space after (1)
- delete the data that appears on right (for example: NT53575, NT53574 and so on) and the white space that appears before this information

Basically if I did a query of this field I want to see:

NT-53575
NT-53574
NT-53573
NT-53572

Thanks!

sikyala,


update yourtable
   set col1 = substr(ltrim(substr(col1, instr(col1,')')+1)),1,instr(ltrim(substr(col1, instr(col1,')')+1)),' ')-1)

Good luck!
DrSQL

In that case I would advice you to use following function,

--------------------------
-- Author    - JB
-- Date       - 23-Aug-2005
-- Modified  -N.A.
-- Purpose  -
-- Remarks - I have assumed that we have fixed string Primary key i.e. of 8 Characters
-- Try using following function in SELECT statement first and passing argument as col1
--------------------------


CREATE OR REPLACE FUNCTION my_val (p_string        VARCHAR2)
RETURN VARCHAR2
IS
   v_var   VARCHAR2(100);
BEGIN
   IF SUBSTR(REPLACE(p_string,'-',''),1, 7) = SUBSTR(p_string,14,7)
   THEN
           v_var := SUBSTR(p_string,1, 8) ;
   ELSE
           v_var := p_string;
   END IF;
RETURN v_var;
END my_val;
/


For Col 1 and Col2 related queries should be executed first so extra space will be removed and then try this function OTHERWISE what I would suggest is user my previous comments for col1 and col2 in the above function and manipulate the data.

Cheers !!

JB

JB

I have found your instructions very helpful. I already used the code from case 1 and 2 to remove the number parens and white space on the left side of the data of the ACCENS_NUM field. So I just need to remove the duplicate information (i.e. the whitespace and data that appears on the right side of each row.

This is how the rows look now:

NT-53575     NT53575

I want to remove the white space and information that follows it.

example:

     NT53575

Looking at the responses I have received it isn't clear how I should do this. Thanks for your help.

sikyala

DrSQL

I tried the following code

col2 = substr(col1, instr(col1,' ')+1),  -- grab everything after the first space

and it removed all the data from the field. I don't think it should have done that because there is no white space before the data.

Thank you DrSQL it worked like a charm.

Sikyala

sikyala,
    You are welcome.  Please try to be careful in your selection of the accepted versus the assisted answer.  I'm not by any means complaining about the point split - JB's contribution seems deserving.  I just like to see the answer that gets used as the one that is accepted.  It makes it clearer for those who read the thread later.  And, if I'm mis-reading your last comment, I apologize.  Thanks.

Good luck!
DrSQL