sql help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: sql help

  1. #1
    Join Date
    Feb 2001
    Posts
    203

    sql help

    In Dept table, In "dname" field I have chr(10) and spaces. I want to remove the Front chr(10) and spaces.

    I know that, If I want to replace all chr(10)'s and spaces in that field I can use

    select ltrim(rtrim(replace(dname,chr(10),''))) from dept;

    But I don't want to remove carriage returns in middle of the data.
    I want to remove only if front of the data.

    Example Data:

    Dname
    ------
    chr(10) space space Finance chr(10) Department
    chr(10) space chr(10) space chr(10) chr(10) IT chr(10) Department

    The Change I want to do
    Dname
    ------
    Finance chr(10) Department
    IT chr(10) Department

    Is their any sql function I can use or Do I have to use plsql
    procedure with cursor. Any suggestion will help me. Thanks Guys.
    sree

  2. #2
    Join Date
    Jan 2001
    Posts
    642
    If you just wanted to remove the leading spaces then just use the trim function:
    SQL> INSERT INTO DEPARTMENT VALUES (' IT DEPARTMENT - ');

    1 row created.

    SQL> INSERT INTO DEPARTMENT VALUES (' FINANCE DEPARTMENT OR IT');

    1 row created.

    SQL> INSERT INTO DEPARTMENT VALUES (' MANUFACTURING DEPT ');

    1 row created.

    SQL> COMMIT;

    Commit complete.

    SQL> SELECT TRIM(COL1) FROM DEPARTMENT;

    TRIM(COL1)
    ------------------------------------------------------------
    IT DEPARTMENT -
    FINANCE DEPARTMENT OR IT
    MANUFACTURING DEPT



    Badrinath
    There is always a better way to do the things.

  3. #3
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    I missed the Chr(10)...let me try
    There is always a better way to do the things.

  4. #4
    Join Date
    Feb 2001
    Posts
    203
    Thanks badrinathn for quick reply.

    May be you missed my point.

    In my data, I don't know how many spaces and carriage returns in front of data.

    If I have only one carriage return or one space then I can use DECODE function, But I don't know how many carriage returns or spaces I have in front of that data.

    I am sure that, I have to read each space and do the filter. By using PLSQL code I can pull each record and put in the loop and do the
    substr(dname,1,1) and do the filtering. But I want to know, Is their any sql functions is there to do this job....Like substr,instr, case and decode...etc.

    Sree.
    sree

  5. #5
    Join Date
    Apr 2003
    Posts
    8
    Hi Shree Sri.

    I guess This Following Code will help you out.
    It works in My DB Hope the same for yours.
    ...

    /* SQL> DESC dept
    Name Null? Type
    ------------------------------- -------- ----
    DNUM NUMBER
    DNAME VARCHAR2(1000)

    SQL>
    */
    DECLARE
    temp VARCHAR2 (500);
    v_num NUMBER:=1;
    v_num1 NUMBER:= 0;
    j NUMBER :=0;
    words NUMBER := 0;
    FinalStr VARCHAR2 (500);
    CURSOR c_cur IS
    SELECT * FROM
    dept;
    BEGIN
    FOR i IN c_cur LOOP
    v_num := 1;
    v_num1 := 0;
    j := 0;
    words := 0;
    LOOP
    temp := substr (I.dname, j+1, INSTR (i.dname, ' ', 1, v_num) - j);
    IF RTRIM (LTRIM (temp)) = 'chr(10)' OR RTRIM (LTRIM (temp)) = 'space' THEN
    j := INSTR (i.dname, ' ', 1, v_num);
    v_num := v_num + 1;
    words := words + 1;
    v_num1 := words;
    ELSE
    J := INSTR (i.dname, ' ', 1, words);
    FinalStr := SUBSTR (i.dname, j+1, LENGTH (i.dname) - J);
    UPDATE dept
    SET dname = FinalStr
    WHERE dnum = i.dnum;
    -- DBMS_OUTPUT.PUT_LINE (FinalStr);
    -- DBMS_OUTPUT.PUT_LINE ('Total Words are: '||words);
    EXIT;
    END IF;
    END LOOP;
    END LOOP;
    END;
    /

    ----------

    Thanks,

    Bhavin
    =========
    Still Learning...

  6. #6
    Join Date
    Apr 2003
    Posts
    8
    I 'm Sorry, I can attach my file so that you can read it properly

    Thanks

    Bhavin...

    ============
    Still Learning...
    Attached Files Attached Files

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