PL/SQL :: Split string Into Two Columns By Dash
Apr 17, 2013
I have got this working
decode(SUBSTR(ADDR2_ATTR_1,1,INSTR(ADDR2_ATTR_1,'-')-1),'9999',NULL,(SUBSTR(ADDR2_ATTR_1, 1, INSTR(ADDR2_ATTR_1,'-')-1))),
decode(SUBSTR(ADDR2_ATTR_1, INSTR(ADDR2_ATTR_1,'-' )+1),'9999',NULL,(SUBSTR(ADDR2_ATTR_1, INSTR(ADDR2_ATTR_1,'-')+1)))
but results came a little different.
It came up as
Col1 for 8 - 10 have came up beauiful ...but however the data at Col2 should be in Co1 and have transferred to Col2
Col1 Col2
<null> 10
<null> 33
8 10
View 8 Replies
ADVERTISEMENT
Jul 14, 2011
I've formatted a column (COLUMN column_name format a20) as it was too large, but now when I run the query I get all dashes ####### instead of the values. I've tried to make it bigger again, but I keep getting ###### as values for that column.
View 4 Replies
View Related
Sep 4, 2012
I am trying to search a word which starts with 'FRA' in any columns and any tables.
I am unable to find what is generating a join datasets in the webservice from teh database as it is not apparent within the 100 tables.
I ahve looked into
Re: How to search in all rows and all columns?
Re: SQL Search Query?
but none of these queries is working out for me as I am a user with no tables on its own but rather a user quering other tables.I think its a tweak on which dat a dictionary I can view
select distinct substr (table_name, 1, 14) "Table",
substr (t.column_value.getstringval (), 1, 50) "Column/Value"
from all_cons_columns,
table
(xmlsequence
(dbms_xmlgen.getxmltype ('select ' || column_name
|| ' from ' || table_name
|| ' where upper('
|| column_name
|| ') like upper(''%' || 'fra'
|| '%'')'
).extract ('ROWSET/ROW/*')
)
) t
order by "Table";
running teh above query got me thsi error:
ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
View 4 Replies
View Related
Jun 19, 2012
I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
View 7 Replies
View Related
Mar 14, 2013
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
View 3 Replies
View Related
Jun 16, 2010
I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
View 4 Replies
View Related
Apr 4, 2011
I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
View 12 Replies
View Related
Jan 21, 2011
I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
View 5 Replies
View Related
May 9, 2008
show an ex to use string buffer for select statemnt
View 1 Replies
View Related
Jul 12, 2012
I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
View 8 Replies
View Related
Aug 22, 2012
what are the collections available in Oracle Plsql, what are concepts of collection.
How to Transpose a Table from rows to columns or columns into rows.
DDL and DML concepts.
What is the concepts of statistics in Oracle Plsql.
View 4 Replies
View Related
May 10, 2010
I have a string like below:
string = 'HEADER||MEAL||15'
How to get 'MEAL' string? The length of the string can be various. Means, 'MEAL' can be 'INFLIGHT'. So, i cant use the substr. Is there a function that can recognize the pipeline? so that i can remove all the string before the pipeline and after the pipeline to get the string between the pipeline?
View 9 Replies
View Related
Oct 6, 2010
I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query
MONTH_YEAR RMS RMS_OCC TTL_RMS
---------------------------------------
SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762
and I need to display this as
COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009
--------------------------------------------------------------
RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762
View 3 Replies
View Related
May 22, 2009
I have a comma seperated string say (tr,er,pr) and i have to convert it to ('tr','er','pr'). if there is function coded to do so.
View 1 Replies
View Related
Oct 12, 2007
I have the following set of numbers that i am passing in as one input into a stored procedure.
234,456,234,456,567
Now i want to take this list of numbers and use it in an IN statement:
select * from table where column_a in (P_INPUT);
however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!
View 6 Replies
View Related
Oct 9, 2008
say there is astring "mumbojumbo "i need the count of given string in it
ex:when o is given count shuld be 2 when m is given count shuld be 3
is there any pre defined function for counting a given string ...
View 2 Replies
View Related
Aug 22, 2013
how to get first word from string.example i have string like:-
Jack Bore American
Mark D'suz Australian
Raj
Deniel indian
Some
i am expecting first word as out put like :-
jack
mark
Raj
Deniel
Some
in the same maner if i want should get two words also.
View 36 Replies
View Related
Dec 12, 2010
how to get last 4 characters in a string. But i don't know the length , for example the string is
abcdefghij
i want only ghij.
View 5 Replies
View Related
Aug 30, 2010
I want to create a strung together list of ATM IDs for each ATM Location (as one ATM Location(City) can have many ATMs(term ids) this is to allow transaction facts to be not broken down on several lines depending on how many term ids there are for that ATM Location (whenever a new ATM is set-up, a new row is created in the ATM table).
I know I can string it together using a function but I do not have rights to do it so I created SQL in which I feed in the ATM Location as a parameter. I want to do this for ALL ATMs but that is taking forever - is there any way to optimize the below code.
Select max(term_id),atm_location from (Select
(SYS_CONNECT_BY_PATH(TERM_ID,' ' ) ) term_id,atm_location
from
(select term_id , atm_location
from ATM_TABLE
order by term_id asc
)
Start with TERM_ID IN (Select max(Term_ID) from ATM_TABLE group by
ATM_LOCATION )
View 8 Replies
View Related
Apr 6, 2012
i have a one table with name as sms_tbl having one field name as sms_text and it contains text messages like
sample_text:Welcome to I-Care, your TPA for your<Insurer> health policy Your ID no is <I-Care ID>
in above text i need to insert records from two tables in the place of <Insurer> and <I-Care ID>.
where <Insurer> and <I-Care ID> records are in two different tables
where i have mapping for this records and there is no mapping for sms_tbl and how to insert these two records in above sample_text
View 3 Replies
View Related
Dec 21, 2011
I have strings like
1) ICE_10001 ICE_10002 ICE_10003
2) ICE_10005 ICE_10006
i want to split above strings like
1)ICE_10001
2)ICE_10002
3)ICE_10003
4)ICE_10005
5)ICE_10006
If it is possible in oracle sql
View 15 Replies
View Related
Oct 31, 2013
I want to convert the follow string to date: 2013-12-04 11:35:54.89
View 7 Replies
View Related
Jul 12, 2010
I have the following test case:
create table t(
col1 varchar2(10),
col2 varchar2(100))
[Code]...
I need to spit col2 like this
P0001 aaa
P0001 ddsd
P0001 sds
.
.
.
.
.
delimeter between individual may be #, ' ',',' or';'
How can I achieve this using PL/SQL
View 13 Replies
View Related
Jun 26, 2012
I want to replace numeric values of a specific format with 'X' , find the below example and note that the string in the example only for sample values and the strings may be different.
Eg.
Input String :
Ticket no 12343 , 1234567891234567 , origin-dxb , dest-lhr , 1234 5678 9012 2345 , address - rose wood
bldg 2444 , downtown ,london-33 .
Output string :
Ticket no 12343 , XXXXXXXXXXXXXXXX , origin-dxb , dest-lhr , XXXX XXXX XXXX XXXX , address - rose wood
bldg 2444 , downtown
View 6 Replies
View Related
Feb 7, 2007
if it is possible to randomize a string
for example if i have string value of ('ABC') how could i get Oracle to randomize the output say to 'BCA' for example
View 1 Replies
View Related
Feb 22, 2011
check out following scripts
CREATE TABLE email (
ids VARCHAR2(500))
INSERT INTO email VALUES('1233@gmail.com;3456@gmail.com;0954@gmail.com')
INSERT INTO email VALUES('7658@gmail.com;346@gmail.com;6346@gmail.com')
CREATE OR REPLACE FUNCTION is_same(emailid VARCHAR2) RETURN BOOLEAN
[code]...
how to write --emailid is present in rec.ids THEN this logic in function
View 2 Replies
View Related
Apr 4, 2012
I have to fetch a string which is between to constant strings in a column.
Ex: Test Column
"The Student Record 10101 is deleted"
"The Student Record 10102 is deleted"
"The Student Record 10103 is deleted"
3 rows.
In this i need to fetch only ID from each row.
create table testtable ( TestCol varchar2(4000));
INSERT INTO TESTTABLE VALUES ('The Student Record 10101 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10102 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10103 is deleted');
View 17 Replies
View Related
Nov 22, 2012
I am trying
IF LENGTH(v_final_string) < 3800 THEN
SELECT nvl2(v_final_string,v_final_string
||',' ,v_final_string)
|| temp.temp_string
INTO v_final_string
FROM DUAL;
DBMS_OUTPUT.put_line ('v_final_string=' || v_final_string );
ELSE
EXIT;
END IF;inside a loop.
But it's not concatenating. I am alwas getting empty v_final_string
View 9 Replies
View Related
Sep 26, 2013
I have a string. For example "I have too many files. There are 1000 files. I have to delete them." Sometimes the string can be "I have too many files. There are 115003 files. I have to delete them." Whatever the srting is, I need to change the string to "I have too many files. There are 10 files. I have to delete them." replace the "1000" or "115003" to "10". This portion of the string is always an integer. I use Oracle 11G2.
View 6 Replies
View Related
Aug 28, 2012
I have the following table A that contain one column "MYREC"
MYREC
1253 69889897 89884 891254
568989 89897891 321 698751232
1239892 123358798 7899 58123457
I need to parse the variable column, the issue is that the number of spaces.
The string might start with 2 or more white spaces, that I can get rid of with the LTRIM function..I'm having difficulties with the rest ):
View 5 Replies
View Related
