PL/SQL :: How To Select Substring In Oracle Up To More Than One Specific Character
Nov 12, 2013
How to select a substring in oracle up to a more than one specific characterfor ex : 121.051^NP: FAMILY PRACTICE ( trim the values before ^ ) 121.051^*NP: FAMILY PRACTICE (trim the value before *). with below function I can only get rid of ^ , I want both the specific characters ^ and ^* to be removed at the same time.
SUBSTR(p.phys_sub_grp_2_desc,INSTR(p.phys_sub_grp_2_desc, '^') +1)
View 7 Replies
ADVERTISEMENT
Aug 9, 2012
I have one string 'SWAP_20120224_MEGAMART_MAR_Sales.csv'
I want to get the string between second underscore and third underscore i.e. 'MEGAMART'
How to achieve this in sql statement?
View 4 Replies
View Related
Nov 6, 2010
i have a column full name which has the name of customer in both English and Sanskrit alphabets. I need to susbtring only the English name and exclude the Sanskrit name .
View 2 Replies
View Related
Mar 10, 2010
If i have a CSV file that is in the following format
"fd!","sdf","dsfds","dsfd",
"fd!","asdf","dsfds","dsfd",
"fd","sdf","rdsfds","dsfd",
"fdd!","sdf","dsfds","fdsfd",
"fd!","sdf","dsfds","dsfd",
"fd","sdf","tdsfds","dsfd",
"fd!","sdf","dsfds","dsfd",
Is it possible to exclude any row where the first column has an exclamation mark at the end of the string.
i.e. it should only load the following rows
"fd","sdf","rdsfds","dsfd",
"fd","sdf","tdsfds","dsfd",
I am using Oracle 10.2
View 9 Replies
View Related
May 29, 2012
I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
View 7 Replies
View Related
Oct 17, 2012
This is what I've been trying to do.
I have the following table:
FOOTBALL PLAYERS < ID, NAME, ATTRIBUTE>
100-JIM-TALL
101-BOB-STRONG
102-MARK-SMART
...etc
I want to form a query that regardless of the total returned records, I will be able to specify how many of each "kind" of players I want returned. There are several good reasons that it has to be one query and not many.
View 4 Replies
View Related
Apr 19, 2013
I have a table that has hierarchical data within it. I need to select this data in a specific way, showing the hierarchy.
E.g. Data in table (Key is unique)
Lvl KeyParKey HasChild
1k101
1k200
1k301
1k401
2k34k10
2k22k11
2k24k10
2k13k30
2k52k30
2k35k30
2k13k30
2k11k40
3k56k220
3k109k221
3k67k220
4k61 k1090
Etc etc....
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows:
Lvl KeyParKey hasCh
1k101
2k34k10
2k22k11
3k56k220
3k109k221
4k61 k1090
3k67k220
2k24k10
1k200
1k301
2k13k30
2k52k30
2k35k30
2k13k30
1k401
2k11k40
View 1 Replies
View Related
Jul 24, 2011
I'm using Oracle 9i Enterprise edition, Is there a select statement to view transaction log for specific date?
View 1 Replies
View Related
Sep 26, 2013
how do we know database character set is either single character set or multi character set?
While changing character-set from AL32UTF8 to WE8MSWIN1252 got "ORA-12712: new character set must be a superset of old character set".
Below are steps taken to resolve the issue -
ALTER DATABASE CHARACTER SET WE8MSWIN1252;
i got this error: ORA-12712: new character set must be a superset of old character set
below are the commands executed by me:
SQL> SHUTDOWN IMMEDIATE;
SQL> CONNECT SYS/password AS SYSDBA;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT;
And its working...
I have not done it in proper order. Neither have done ccsscan. Still, no user reported any issues. Do my changes truncated the data?
View 11 Replies
View Related
Apr 11, 2011
I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
View 3 Replies
View Related
Jan 25, 2012
Im trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .
In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.
View 3 Replies
View Related
Mar 18, 2013
I have two tables A and B. In table A there is a field which contains a string of 20 characters; this essentially holds 5 codes of 4 characters each.
Table B is a reference table. It holds the 4 character code and the description.
I am trying to run a select query to bring back the description of the code for the first 2 codes in table A but i am not sure how to bring back the descriptions! The below is what i am trying to achieve.
SELECT
TableA.ID,
SUBSTR(TableA.Code,1,4) Primary_Code,
[Code].....
View 6 Replies
View Related
Mar 7, 2011
Is it possible to substring an alias field?
I am looking to substring the following so that the age comes back as 2 digits rather than an indefinite number
months_between (c.fatt,b.birth_dt)/12 age
View 2 Replies
View Related
Jan 2, 2012
I have a reference number of application number which is sent to 3 different address as outward number . for example application number is intimated to the office , the employee and the treasury. this number is generated as follows
P/11/10/133
P/11/10/133A
P/11/10/133B
in my outward report when i run it all three numbers are getting listed.
i want only P/11/10/133 to be listed and ignore the rest of the two. i tried to use SUBSTR(INOUT_NO,9), but it reads only from the left gives me only the number ie 133, 133A and 133B. i tried rtrim and ltrim but it only clears the extra space. how do i ignore the other two dispatch numbers.
View 2 Replies
View Related
Oct 20, 2010
I have a column transaction Number . It has the transaction number of goods sold.. Below is the sample.
1. PIT0120029015554492215851181828221018554492R06
2. XY1029201195J05
3. YJ1039201176J01
My Query :
I need to substr the transaction number which starts with PIT to susbst (trxno,1,12) .. and anything other than PIT i need full number without substr . But when i use the above code :" susbstr(trxno,1,12) ..": it will substr entire column,. Is there any way to substr only PIT and leave others,
DB:oracle 10g
View 7 Replies
View Related
Oct 23, 2012
declare Input VARCHAR(10) :='011000141'
declare rt StringOutput%rowtype;
begin
SELECT SUBSTR(Input , SumOfLength - ColLength + 1, ColLength) FinalOutput INTO rt FROM mytable ;
end;
/
I need to store the result to a temp table rt.
View 10 Replies
View Related
Sep 21, 2012
I've got this situation.
In a not normalized table loaded with data from an excel file, I have a column containing a long string with many carriage returns so that after each of them it begins a new string I have to manipulate. I mean, in the column PERSON I've got something like this:
PERSON
_______
Jonh
Taylor
Software developer
...
Of course I have to write a Pl/Sql procedure extracting each string and I know that each string ends with a carriage return chr(13)||chr(10)
I think I should use the INSTR built-in function but I it'd be useful whether some of you had something ready.
View 15 Replies
View Related
Apr 25, 2010
I am currently doing a project where i need to write a stored procedure which will be doing the following-
i)it will retrieve multiple columns from multiple tables in a single database(through join) based on certain conditions
II)then it will store the entire data in a certain field(File_data) of staging table
inside file_data a header and a trailer will be present with the records.also the field values will be pipe separated and a new record will start in a new line.
So,the data inside the file_data of staging table will look like this-
H|v1000
transdate|ordnmb|deposite_amt|order_status....
12-nov-09|123456|23.8|C...
4-dec-07|234567|67.7|R...
..........
7-jan-04|567890|54.7|x.....
T|234(record count)
i did this formatting using java, but my project leader wants me to do the formatting using SP,and wants me to use staging table.
View 7 Replies
View Related
Nov 25, 2012
the more customers we have for our software solution the more individual oracle objects (Tables, Packages, Functions, etc.) we have in our scheme. Right now we separate these by giving them identifying names like "X_CUSTOMER1_TABLENAME" e.g. (I know ..... )
This is not very practical when keeping our reference clean and when deploying/syncing our reference with a customer db: One customer would receive the objects of other customers on a deploy.
Is there a common solution to this problem? We were thinking about having a separate scheme for each customer. That way we would have our standard, untouched scheme with the basic functionality and the customer schemes with the individual content.
To make it a bit more concrete: We have around 100 basic tables that make the most of the content/functionality of the software. Each customer might have between 1 -5 additional tables with "custom" data that is used in conjunction with the standard objects in individual packages, functions etc. The installations will be made on our customers systems. So I have in mind to have schemes 0001, 0002, etc for each customer IN OUR REFERENCE. But we would then deploy only the scheme for that certain user when installing on their system. So for example for customer 0001 I would deploy the STANDARD and 0001 scheme
View 6 Replies
View Related
Aug 1, 2012
I'm having some difficulty securing my Oracle APEX + Oracle HTTP Server environment.
There are currently around 20 Applications running on the APEX Server, and I'd like to publish 2 of them Externally for users.
I have added a certificate to Oracle Wallet Manager to secure the OHS, but I can access all the Applications on the APEX Server by just changing the Application ID suffix.
How can allow only two applications to be accessible via the internet?
View 11 Replies
View Related
Jan 18, 2013
I was asked if it was possible to restrict which users / or client IP's connect to my Oracle 11.2 database. I guess I could just shutdown the listener and have me and one other DBA connect to it via SSH / LOCALHOST but I was wondering if there was a more DBA specific way to restrict client connections to just two specific IP's over the Oracle listening port?
View 5 Replies
View Related
May 25, 2010
We have Oracle 10g and user trying to Insert following in one of our table field and getting error:
'REVOLUCIÃ"N Historical Corruptions Agenda'
Getting follwoing error:
ORA-00911: invalid character
create table Employee
(Emp_ID Varchar2 (10),
Company_Name Varchar2 (40)
)
Insert into Employee
values ('Emp1', 'REVOLUCIÃ"N Historical Corruptions Agenda');
View 5 Replies
View Related
Jul 22, 2013
I have an application connected to Oracle 11g that sends its own querys to the db based on what the user is clickng on. The applicaiton is connected via one user id and I was wondering, is there a way that I can capture the tiem each query starts, the sql itself, and the amount of time it took to fetch the data?
View 7 Replies
View Related
Mar 8, 2007
I'm trying to insert a character from the extended ascii character set. Specifically, there's a company that has an accented e (�) in the name. Right now, the company name doesn't have the e at all, accent or no accent. So I'm trying to do an update, something like
update table1 set company_name='blah�" where company='blah'
It runs, but doesn't do the update. Even when I try to forcefully do an insert (instead of an update) I get nowhere; the accented is simply dropped. So the basic question is, how do you insert extended ascii characters into oracle?
View 3 Replies
View Related
Feb 9, 2013
I am using C++ OCI LIB, to insert some report data from remote OCI client to oracle 11 server. This data is read by another process to create the report.The DB CHARSET is UTF-8. But the report tool expects the data to be ISO08859-1 encoded. So while inserting the data into the database i specify the following LANG and CHARSET for my table colulmn in client:
The TARGET DB CHARSET is UTF-8
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
size_t csid = 871; // UTF-8
OCIAtrSet((void *) bnd1p, (ub4) OCI_HTYPE_BIND,
*(void *)&csid*,
(ub4) 0,
(ub4)OCI_ATTR_CHARSET_ID, errhp);
This solution works for almost every case of ASCII and Extended ASCII Charest but we are facing issues if we have few specific characters to be inserted.f we are trying to insert single beta character [β] through client, the data goes empty to the column.
Beta Character details:
DEC OCT HEX BIN Symbol Description
223 337 DF 11011111 ß Latin small letter sharp s - ess-zed
DB Output after insert single β:
select rawtohex(NAME) from PERSONS where EID=333;
RAWTOHEX(NAME)
---------------------------
But if the string is *"ββ"* everything work fine:
DB Output for "ββ":
select rawtohex(NAME) from PERSONS where EID=333;
RAWTOHEX(NAME)
---------------------------
DFDF
View 6 Replies
View Related
May 21, 2010
I am using Oracle 10g version and I have a database created and it contains around 9 tables and 25k records, as per my current requirement I need to add few more columns in the existing tables and those new columns are going to be filled with contents from multiple languages (as of now Chinese and English). I went through Oracle 10g globalization guide and I understood that, for my requirements I need to add my new columns with data types of either NVARCHAR or NCHAR.
With my about understanding I created the following table and tried to insert some Chinese characters as follows but it's now coming as expected and I'm getting inserted only "????" in my table columns
Find below the list of actions
>create table Employee(EmpId varchar(255), EmpName NCHAR(255));
>insert into Employee(EmpId, EmpName) values('280129','彭俊睦');
>select * from Employee;
280129 ¿¿¿
>insert into Employee(EmpId, EmpName) values('28018',N'彭俊睦');
>select * from Employee;
280129 ¿¿¿
28018 ¿¿¿
When I run the “select * from v$nls_parameters;” query I’m getting the following data
NLS_LANGUAGE ---------à SIMPLIFIED CHINESE
NLS_TERRITORY---------à AMERICA
NLS_CURRENCY---------à $
NLS_ISO_CURRENCY---------à AMERICA
NLS_NUMERIC_CHARACTERS---------à .,
NLS_CALENDAR---------à GREGORIAN
NLS_DATE_FORMAT---------à DD-MON-RR
[Code] .....
View 3 Replies
View Related
Feb 20, 2012
I am running an Oracle 9i DB with character set : FRENCH_FRANCE.WE8ISO8859P1.
The data inside the DB deals with two languages :
- French and Arabic.
I insert, delete, update data on the DB through one of these tools :
- SQL*PLUS
- Forms 6i
and everything works fine especially with Arabic : characters are interpreted correctly by these tools. Now, I want to use Dev Suite as a new tool.
My problem is with Arabic characters : they are not displayed correctly. On the web form (run by a JRE : JInitiator) the characters are not replaced by "?", they are wrongly interpreted.
Example :
The right interpretation of "Amine" in arabic is : أمين and not : Ããíä.
So, querying a table with Dev Suite returns me Ããíä and not ????.
It seems like the JRE "understands" the characters but doesn't know how to "interpret" the Arabic characters correctly. I've tried to play with the NLS_LANG parameter on the regedit but in vain.
View 6 Replies
View Related
May 9, 2011
I want to insert some Chinese character in a row of my master table and also retrieve the same to display on web page along with others.
View 2 Replies
View Related
Sep 17, 2012
A database containing inventory data has been migrated from Oracle 10g to Oracle 11g. I have access to both the Oracle 10g and Oracle 11g database on different client computers. Both databases use the same character set, WE9MSWIN1252 (query shown below). However the results from the sql SELECT show incorrectly displayed characters. I would like the "1/2" character and degree character to show in the text. The ASCIISTR function shows that the underlying ascii is the same in the two copies of the databases.
Is there a setting that needs to be changed in Oracle 11g so that the saved special characters in the database show correctly (as in Oracle 10).
Query of database character set
SQL> Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET'
WE8MSWIN1252
Under Oracle 11g, this is a query on DSI using SQLPLUS 11.2.0.1.0.
SQL> select description from part where id = '57234';
DESCRIPTION
----------------------------------------
KL BRKT PLN 22╜░ ANGLE (AMER BOT RAIL)
SQL> select asciistr(description) from part where id='57234';
ASCIISTR(DESCRIPTION)
--------------------------------------------------------------------------------
[code].....
View 6 Replies
View Related
Apr 15, 2011
Can I make charatcer report in 10g web application like 6i
View 1 Replies
View Related
