-
clean up data
Hi friends,
My database contained garbage like the char '-' in the NAME column.
When I tried to update it to null it can not be found or selected.
UPDATE EMP SET NAME=NULL WHERE NAME='-';
0 rows updated.
Help me please how to handle this character.
Thanks
-
Re: clean up data
Originally posted by kris123
UPDATE EMP SET NAME=NULL WHERE NAME='-';
Replace the above with a LIKE clause...
Code:
UPDATE EMP SET NAME=NULL WHERE NAME LIKE '%-%';
BTW, do a SELECT and verify it is what you want to do and then do the UPDATE.
HTH.
-
Thanks friend,
Can I use the wild card '%' in decode statement?
You know I got lots of columns that contain the char '-'
and wanted to change it to null. So instead of doing...
update emp set field1=null where field1 like '%-%';
update emp set field2=null where field2 like '%-%';
update emp set field3=null where field3 like '%-%';
.
.
.
I tried
update emp set f1=decode(f1,'%-%',null,f1),
f2=decode(f2,'%-%',null,f2),
f2=decode(f2,'%-%',null,f2),
...
but it did not work, Is there other way to do this?
Thanks
-
Originally posted by kris123
I tried
update emp set f1=decode(f1,'%-%',null,f1),
f2=decode(f2,'%-%',null,f2),
f2=decode(f2,'%-%',null,f2),
...
but it did not work, Is there other way to do this?
Use this one:
Code:
UPDATE emp SET f1 = DECODE(INSTR(f1,'-'),0,f1,NULL),
f2 = DECODE(INSTR(f2,'-'),0,f2,NULL),
f3 = DECODE(INSTR(f3,'-'),0,f3,NULL),
...
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks Jurij.
-
Hi there helpful friends, need your help again.
Can you help me how to validate a "DATE" column that contains invalid date? Say, I have an EMP table with column
HIREDATE VARCHAR2(6), with mmddyy format. It is declared
first as varchar to accomodate all erroneous data loaded
from input text file.
sql> select hiredate from emp;
HIREDATE
--------
010103
-
013203
%^$&**
I want to display only the invalid dates using select statement.
Can you help me pls.
Thanks
-
Create a function that checks for valid/invalid dates:
Code:
CREATE OR REPLACE FUNCTION is_date (p_string VARCHAR2)
RETURN INTEGER
AS
v_dummy DATE;
BEGIN
v_dummy := TO_DATE(p_string, 'MMDDRR');
RETURN 1;
EXCEPTION
WHEN OTHERS THEN RETURN 0;
END;
Then select all the records with invalid "dates":
SELECT * FROM emp WHERE is_date(hiredate) = 0;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
IMPRESSIVE! Thanks a lot and God Bless everyone.
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
|