PL/SQL :: Check If Data Stored In Varchar2 Type Column Is Date Or Not
Jun 29, 2012
Is there a seeded function by which I can check all the rows which stored dates in varchar column.
I have a table say test (test_data varchar2(100));
Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only
INSERT INTO test(1);
INSERT INTO test('ABC');
INSERT INTO test(SYSDATE);
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY'));
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test('15/01/2012');
...
commit;
I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column
like '%abc%' like 'abc%' like '&abc'
Will be the corresponding index will be for those cases?
title varchar2(100) y publisher varchar2(20) y categoryname varchar2(20) y rating varchar2(2) y
my query is, select ROWNUM AS "Rank",title,publisher from (select rating,title,publisher from bookshelf_test order by rating desc ) where ROWNUM <=3
returns result ,
1 1 MY LEDGER KOCH PRESS 2 2 TO KILL A MOCKINGBIRD HARPERCOLLINS 3 3 THE MISMEASURE OF MAN W.W. NORTON
But inner query (select rating,title,publisher from bookshelf_test order by rating desc ) returns,
1 5 WONDERFUL LIFE W.W.NORTON 2 5 THE MISMEASURE OF MAN W.W. NORTON 3 5 TO KILL A MOCKINGBIRD HARPERCOLLINS 4 5 MY LEDGER KOCH PRESS 5 4 TRUMAN SIMON SCHUSTER 6 4 GOSPEL PICADOR 7 4 HARRY POTTER AND THE GOBLET OF FIRE SCHOLASTIC 8 4 INNUMERACY VINTAGE BOOKS 9 4 JOHN ADAMS SIMON SCHUSTER 10 4 JOURNALS OF LEWIS AND CLARK MARINER 11 4 LETTERS AND PAPERS FROM PRISON SCRIBNER 12 4 PREACHING TO HEAD AND HEART ABINGDON PRESS 13 4 THE SHIPPING NEWS SIMON SCHUSTER 14 4 THE GOOD BOOK BARD 15 4 THE DISCOVERERS RANDOM HOUSE 16 3 THE COST OF DISCIPLESHIP TOUCHSTONE 17 3 SHOELESS JOE MARINER 18 3 KIERKEGAARD ANTHOLOGY PRINCETON UNIV PR 19 3 EMMA WHO SAVED MY LIFE ST MARTIN'S PRESS 20 3 EITHER/OR PENGUIN 21 3 CHARLOTTE'S WEB HARPERTROPHY 22 3 BOX SOCIALS MARINER 23 3 ANNE OF GREEN GABLES GRAMMERCY 24 3 WEST WITH THE NIGHT NORTH POINT PRESS 25 3 UNDER THE EYE OF THE CLOCK ARCADE PUB 26 3 TRUMPET OF THE SWAN HARPERCOLLINS 27 2 COMPLETE POEMS OF JOHN KEATS VIKING 28 1 POLAR EXPRESS HOUGHTON MIFFLIN 29 1 GOOD DOG, CARL LITTLE SIMON 30 1 MIDNIGHT MAGIC SCHOLASTIC 31 1 RUNAWAY BUNNY HARPERFESTIVAL
why final queries top 3 rows r different than inner query ?
I have table called INFO and the column called CREATED_DATE . Now the datatype of CREATED_DATE is of varchar2 . Now If I need to query the table through select statement where I need to order the result based on CREATED_DATE , how can i achieve this ?
I have table customer which contains a column CUSTOMER_FIRST_NAME
CUSTOMER_FIRST_NAME VARCHAR2(50)
What will be sql statement to add a constraint on the CUSTOMER_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers ?
I have a table with modifieddate column with 'DATE' data type.I am facing date format exception and tried with to_char, to_date but its throwing invalid number exception. how to format date accordingly.
SELECT * FROM EMP WHERE modifieddate > '31-Dec-2011 18:30';
ORA-01722: invalid number 01722. 00000 - "invalid number"
i have a varchar2 column containing string values that can be converted to date i.e. ('31-JUL-11') and that column also contains text strings in it. i.e. ('Some string data...')
records whose column value can be converted to date are extractable via where clause (i.e. those rows are associated with some fix number / flag)
now when i try to use to_date function i get the error that
" ORA-01858 a non-numeric character was found where a numeric was expected "
in sql i have added a where clause to only pick rows with flag, but even then it gives the error.
using a subquery in the from clause eliminates the error, but when i create it in a view it again gives the same error.
I have a column defined as Number( 8 ) which is supposed to have date values. I would like to check if all the rows in that table have valid dates. We could use to_date(coulmn_name, 'YYYYMMDD') and catch the rownums for error conditions using pl/sql. I would like to know if we could just do it using sql only and return the row numbers for those that are invalid dates?
I am using the same query on a different table and there's no problem on it:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM where ENTRYDATE > sysdate -10 and rownum <10 03-03-2011 17:06:48 03-03-2011 17:06:48 03-03-2011 16:59:08 03-03-2011 16:59:08 03-03-2011 12:04:21
I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?
I am trying to run select query on xml using xmltable. One of the tag returns date and I want to take it in date format in xmltable column.
select t.* from XMLTYPETEST xt , XMLTable ('/TestData/trade' passing xt.data columns test varchar2(100) path 'GenTest/Id', testdate date path 'DateTest/Date')t;
But its throwing error as -
SQL Error: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine ORA-01830: date format picture ends before converting entire input string 29958. 00000 - "fatal error occurred in the execution of ODCIINDEXCREATE routine" *Cause: Failed to successfully execute the ODCIIndexCreate routine.
i am fairly new in the oracle arena, but what would cause a statement such as
ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL
to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.
First, I'm aware that the equals (=) operator is a "comparison operator compares two values for equality." In other words, in an SQL statement, it won't return true unless both sides of the equation are equal. For example:
SELECT * FROM Store WHERE Quantity = 200; The LIKE operator "implements a pattern match comparison" that attempts to match "a string value against a pattern string containing wild-card characters."
For example:
SELECT * FROM Employees WHERE Name LIKE 'Chris%';
Here,I query about date type data on ORACLE database, I found the following, when I write select statment in this way:
SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE FROM ACCOUNT WHERE ACCOUNT.LAST_TRANSACTION_DATE LIKE '30-JUL-07';
I get all rows I'm looking for. but when I use the sign equal =
instead :SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE FROM ACCOUNT WHERE ACCOUNT.LAST_TRANSACTION_DATE = '30-JUL-07';
I get nothing even though nothing is different except the equal sign.
I have a table with two columns called startsem and gradsem they are both CHAR(3). Within those colums are rows that are listed as SemesterYear. For example, F09 is Fall 2009, S09 Spring 2009, and M09 is Summer 2009. I would like to create a constraint that says GradSem must be greater than StartSem b/c no one can travel back in time to graduate. However, as you know you can compare S09 > F09 because it will treat it as a string. I thought I could use a substring and compare the last two digits as a year and that would work but how do I compare the semesters as a time frame? Because in my schema F > S because Spring 2011 comes before Fall 2011 but in reality F < S because to Oracle it is a string and the ASCII value of F is less than S. I cannot chage the coding of the database so editing the rows so they are more date friendly is not an option.
So how can I modify this database to acruately compare StartSem and GradSem.
I am trying to name a column from data stored in a row (Phone number) I want to name it from the first 3 digits (Area code) and I have no clue how to do it I have googled but I think the query is a little too complicated for me to explain it and get a result from google or find it in a section of a book.
My phone numbers are strings, so I initially look for the first 3 characters and then start separating and counting, I can do that, the only problem I have is naming the column WITH the the first three digits that I just separated.
I am trying to name a column from data stored in a row (Phone number) I want to name it from the first 3 digits (Area code) and I have no clue how to do it I have Googled but I think the query is a little too complicated for me to explain it and get a result from Google or find it in a section of a book.
My phone numbers are strings, so I initially look for the first 3 characters and then start separating and counting, I can do that, the only problem I have is naming the column WITH the the first three digits that I just separated.
I am trying to learn by myself how to use databases and am using the HR database that comes in oracle since I read in a forum that it is a great way to learn how to use queries.
I read about using Pivot to do it, but in pivot I need to know how many area codes there are. I will post a screen shot of the table that I am working on the other post
[URL].....
I can group by phone number with this:
SELECT SUBSTR(PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(PHONE_NUMBER, 1, 3)) TOTAL_COUNT FROM EMPLOYEES GROUP BY SUBSTR(PHONE_NUMBER, 1, 3);
But what I am interested in doing is that instead of Area appearing as a column, that the actual first 3 digits would appear as the column name.
This is kind of what I am trying to do, but instead of AREA appearing as column name I would like for each one of the area codes to appear as column name, here is the
SELECT DE.DEPARTMENT_NAME, EM.SEX, SUBSTR(EM.PHONE_NUMBER, 1, 3) AREA, COUNT(SUBSTR(EM.PHONE_NUMBER, 1, 3)) TOTAL_COUNT FROM EMPLOYEES EM, DEPARTMENTS DE WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID GROUP BY DEPARTMENT_NAME, EM.SEX, SUBSTR(PHONE_NUMBER, 1, 3) ORDER BY DEPARTMENT_NAME;
I have to create a table which should store data at Week level. The table have the following columns
Product id, Loc id, Business group id, FISCAL WEEK , Revenue,
Fiscal week column will have data as '2011-W01', '2011-W47' etc.
What should be the data type for fiscal week column. Based on this table i have to create a calculated column which should fetch trailing 12 weeks average for each row.
I have extracted in the following XML document some Mpeg7 visual descriptors from an image and I saved it in an XMLType column. I would like to use XMLQuery to extract the data from the Value node. how write such a query. I could not get the proper Xpath to any node.
When we try to query a column from a table the output is shown as "?????". Seems the data in that column is stored in a different character set, which is not recognised while displaying the output. The column size is 35byte.
The nls parameter table shows the character set as "we8mswin1252".
I have a task to update one of the rows in a table (having only 2 columns, number and long) which is long data type. We are on Oracle 10g. Not sure how to use update for a long data type column.
I have tried using dbms_metadata_util.long2varchar, but still not getting what I want.
