I'm trying to extract the domain from email addresses like from clay500@msn.com I want to pull msn
I figured I would use the POSITION function, but it fails, even when I cut and pasted the example from the syntax guide
SELECT POSITION ('CAT' IN 'CATCH') FROM DUAL;
the guide says you get the answer 1
but I get ORA-00907: missing right parenthesis
What gives?
I have a field for email address. I would like to create a field to capture just the domain (after the @ and before the .com) so I can process a count of records by domain.
View 17 Replies View RelatedI am writing a REGEXP_LIKE function to validate the email address.. below function works for most of the valid email address, but not working for below condition... not sure how it can be tweaked to work for below condition as well...
Character . (dot, period, full stop) provided that it is not the first or last character, and provided also that it does not appear two or more times consecutively (e.g. John..Doe@example.com).
Select 'X' from dual
WHERE NOT REGEXP_LIKE('John.Doe@example.com','^(([a-zA-Z0-9_-])([a-zA-Z0-9_.''-]*)@([((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9]).){3}|((([a-zA-Z0-9-]+).)+))([a-zA-Z]{2,}|(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])]))$')
code to extract email id (full email id) from a string/text using Oracle Regular Expressions.
Example -
"This is my String with email abc.efg@hij.com to test" O/p - abc.efg@hij.com
Due to some some Network issues . we have planing to move oracle database one domain to another domain changing domain name and IP address of oracle database . if want to change oracle database 10g(10.2.0.5.0) 32 bit windows version what are setting s to change in oracle database.
View 3 Replies View Relatedhow to send email (PL/sql Procedure) to private domain through google mail.
View 2 Replies View RelatedI want to know how can I find/take from In parameter who is xmltype an email address ?
My function: (clob is cast to xmltype)
function submit(session_id in number, claim in clob) return xmltype
For example - I have more e-mail addresses in this:
<emails>
wtfn00b1@yahoo.com
wtfn00b2@inbox.com
wtfn00b3@mail.com
...
</emails>
I know that there is `extract` function for xml. How can I know if there is break or space between the emails ?
How do I select the name in an email address. I would like to be able to order a list of email addresses by the name of the person ie. what comes before the @ sign.
So is there a query I could use to get everything before the at line . So in pseudocode I would need to be able to do something like this.
select substr(email, indexOf(@)+1,email.length) from table order by email
So I how could I go about doing that?
I want only one email address should be entered in the Alphanumeric field and not any words or phrases or spaces or any other email address.
How to give restriction for this.
i want if user is not type the @ in email text box than from show the message "check your email address" but i cant do that
declare
ok_flag number :=0;
begin
ok_flag :=instr('emailaddress','@');
if
ok_flag < 1 then
message('invalid email');
else
message('thanks');
end if;
end;
i have created a registration form with an input field of email address. now i want if a new user register, it should automatically receive a confirmation mail.
i have done some coding but i am unable to define what to write on place of highlighted text.
public void sendConfEmail(){
try{
Properties props = null;
if (props == null) {
props = System.getProperties();
}
props.put("mail.smtp.host", "<server host name>");
Session session = Session.getInstance(props, null);
[code]..
I have sample windows C# application to connect remote Oracle DB(Enterprise edition SQ L plus).
Want to connect remotely without installing oracle client on local system.
I need to extract the First portion of date entered in remarks column , i have prepared a test as below.
CREATE TABLE LEAVE_DYS (LV_EMP_CODE VARCHAR2(12), LV_DESC VARCHAR2(2000));
INSERT INTO LEAVE_DYS VALUES ('R0754','LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0667','LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0841','LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)');
INSERT INTO LEAVE_DYS VALUES ('R0854','LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)');
[code].....
--Required output is
LV_EMP_CODE LV_DESC
R0754 16/04/2012
R0667 14/07/2012
R0841 29/05/2012
R0854 29/05/2012
R0313 09/09/2012
I have following column data
NZ(abc_mode)/ (NZ(bch_mode) + NZ(cdh_mode)
Now i need query to extract the string inside the braces NZ() with or without using regular expressions in SQL
the output should be
abc_mode
bch_mode
cdh_mode
I am using this Regexp to extract numbers from a string, and I doubt that there is a more efficient way to get this done:
SELECT regexp_replace (regexp_replace ( REGEXp_REPLACE (' !@#$%^&*()_+= '' + 00 SDFKA 324 000 8702 234 | " ' , '[[:punct:]]',''), '[[:space:]]',''), '[[:alpha:]]','') FROM dual
{code}
Is there a more efficient way to get this done ?
I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.
View 3 Replies View RelatedI need to extract values from string to variables as below.
declare
str varchar2(100):='Acknowledgment=1234,Order Requester=5678,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
l_a varchar2(100);
l_or varchar2(100);
l_s varchar2(100);
l_ot varchar2(100);
l_pre varchar2(100);
Begin
l_a:='1234';
l_or:='5678';
l_s:='9999';
l_ot:='1456';
l_pre:='1234,23445,56767';
end;
/
But here challenge is order of alignment change dynamically. ex as below.
str varchar2(100):='Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
str varchar2(100):='Pre=1234,23445,56767,Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456';
So how to extract Acknowledgement to l_a,Order requester to l_or ...
I have some strings like
'Net Amount Payable by an Individual',
'Net Amount Payable by an Individual+Tax',
'Total Amount Payable towards Service',
'Total Amount Payable towards Service.+Tax'
I need to extract the first three letters from each word and separate them using an underscore. The output should be as follows for the above strings -
Net_Amo_Pay_by_an_Ind
Net_Amo_Pay_by_an_Ind_Tax
Tot_Amo_Pay_tow_Ser
Tot_Amo_Pay_tow_Ser_Tax
I request to let me know the way to get the ouput as mentioned.
I have two tables emp , emp address as below
emp table :
emp_id emp_name,
1 rajesh
2 suresh
emp address table :
emp id emp_addresstype emp address
1 O kukatpally
1 H Hitech
2 O Kolkata
2 H hydar nagar
I need the query to display the Office address & House address & emp id in single query
ex: 1,Kukatally,hitech
2,kolkata,hydarnagr
I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
I have a string like 10.01.03.04.234 or 234.05.07.032.� Is there a simple function in Oracle that would give me
10
01
03
04
234 as seperate values in a select clause.
I would like to do a
Select function(10.01.03.04.234)
into var1, var2, var3, var4, var5
from ....
I can accomplish the same with substr/length/instrs in a procedure...but looking for a simple function, if any, instead of writing a proc/function....
i m create one function in which i have to concatenate result likeDate is 19/07/2010'. i tried to concatenate in select statement. in this case function is created successfully but it gives error when it run.
CREATE or replace function TEST(FINYEAR VARCHAR2)
RETURN VARCHAR2 AS
QRY_STRING VARCHAR2(1000);
BALANCE VARCHAR2(100);
[code]...
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at "TEST", line 11
Years ago, someone created a database in Oracle that was carried over and now sits in Oracle 10g. I am developing an application that queries this database and returns the result as XML.
Many (thousands) of rows have an item description that contains an ampersand. I want each of these pieces of data to have it written as & amp; (had to add a space so it would show up here, but you know what I mean) instead of &, but I don't feel like doing thousands of UPDATEs to change this.
Does Oracle have any global find/replace functions that I can call? I'd rather do one update statement that replaces all occurances of & with & amp; but I can't seem to find a function that will do this.
I'm thinking something that would work like:
UPDATE table SET column1=REPLACE(column1,oldstr,newstr);
I have a package includes 22 functions, each function just returns a sql template (clob type).
I also have a stored procedure called query_builder, query_builder has applicationName and statementName as parameters. I need to call these functions in the package based on the given applicationname and statementname.
CREATE OR REPLACE PROCEDURE Query_builder (ApplicationName varchar2, StatementName varchar2) IS
SQLSkeleton varchar2;
BEGIN
PackageName := ApplicationName||'_SKELETON;
SQLSkeleton := PackageName.StatementName; -- I know this will not work, but how can i call these function dynamically?
I'm trying to find a simple way of getting around this. I have a PL/SQL procedure which loops through a list of values in a table. These values are the actual names of the functions I want it to run in sequence. The problem is I can get the string value each time but I need to store the return value of each function into a number variable e.g.
BEGIN
open all_tests;
fetch all_tests BULK COLLECT INTO test_tabl;
close all_tests;
for myindex IN 1..test_tabl.count LOOP
time_taken := test_source1 ==> this will work but how do I avoid hardcoding the name of the function to be executed and use the test_name value instead?? time_taken is declared as a number (ie the return value of each function is a number)
test_detail := test_tabl(myindex).test_name; ==> test_detail now contains the string name of the function
dbms_output.put_line('Test detail is ' || test_detail);
end loop;
I just think about write a function with gives me a searched string from table.
select col1, col2 from the_table
col1 col2
--------------------------
SMS yes
Melodies no
Java build
...
And I would like to make a function or something like that where input parameter is from col1 and result is col2. For instance:
select the_function('SMS') from dual;
Result: yes
how to write it?
I have created a VIEW with a column containing user-defined aggregated values(stores the output in varchar2 data type). Some of the records exceed the maximum limit, for example varchar2(4000). I was suggested a PL/SQL function to check the length and build a string if necessary..
CREATE OR REPLACE
FUNCTION SUBURL(col1 IN VARCHAR2, col2 IN varchar2)
RETURN varchar2
[Code].....
Here, I got the following error,
Error(4,25): PLS-00103: Encountered the symbol "," when expecting one of the following:
:= . ( @ % ; not null range default character
I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function
Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)
CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED
[code]....
But here I am facing 2 problems.
1. The function is not accepting a large string & I am getting the error
ORA-01704: string literal too long
2. The function is going for an infinite loop.
I am on 11g.
I need to remove the alpha characters from a string, leaving only numbers, but I am getting unexpected results:
SQL> SELECT TRANSLATE('3N', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL) a FROM DUAL;
A
-
I thought this would leave the 3 from the 3N, but it is returning an empty string. For my application, the string '3N' could be any length, will only contain letters and numbers, and the letters will always come at the end, but there could be more than one letter
VALID INPUT samples:
4
25
11F
361NG
8ABC
I have a table in my application that has the following structure
Create table EVENT (project_id number, DISC_CMPLNT_TX clob, MAINT_ACT_TX CLOB,LGBK_CMT_TX VARCHAR2(1000 CHAR))
PARTITION BY RANGE (project_id)
(PARTITION pmax VALUES LESS THAN (MAXVALUE));
I have created the below preference on the three columns of the table.
begin
ctx_ddl.create_preference ('skipJoinLexer', 'BASIC_LEXER');
ctx_ddl.set_attribute ('skipJoinLexer', 'skipjoins', ';:.,()<>*"''#-');
ctx_ddl.create_preference ('logbookTextDS', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute ('logbookTextDS', 'COLUMNS', 'DISC_CMPLNT_TX, MAINT_ACT_TX,LGBK_CMT_TX');
end;
I have created a local partitioned index with the parameter NOPOPULATE create index lb_text_idx on ac_maint (DISC_CMPLNT_TX) indextype is ctxsys.context local parameters ('lexer skipJoinLexer datastore logbookTextDS NOPOPULATE');
When an new value of project_id is inserted into table1 , I create a partition using the following command where prjId is the new value of the project_id ALTER TABLE EVENT split PARTITION pmax AT ('||prjId||') INTO ( PARTITION p_' || prjId || ', PARTITION pmax)
Now I run a huge data load into the EVENT table for a project Id say 1 and insert millions of records.I want to rebuild the lb_text_idx for this partition alone so that data is available for searching by the application. I did a rebuild using the command " alter index lb_text_idx rebuild partition p_1".
However when I run the below sql, I get a count of 0. I know that the text MAINT is definitely present in the table.
SELECT COUNT(1) FROM EVENT WHERE PROJECT_ID = 1 and contains (DISC_CMPLNT_TX, 'MAINT' ) > 0
When I run the below sql select parameters,status from user_ind_partitions where partition_name = 'P_1' , I get parameters as null and status as usable.Am I getting 0 records because the parameters are set to null? How do I rebuild with the same parameters for the specific partition alone.
