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
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.
Hi,
I missed the Chr(10)...let me try
There is always a better way to do the things.
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
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...
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
Forum Rules
Click Here to Expand Forum to Full Width