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.

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

Hi,

I missed the Chr(10)...let me try

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.

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...

I 'm Sorry, I can attach my file so that you can read it properly

Thanks

Bhavin...

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