SQL & PL/SQL :: Concatenating Address Fields
Oct 28, 2013
The pipe separator needs to appear only when values found in addr1 or addr2 or addr3.
WITH address AS (SELECT 'Silver Arc Plaza,' addr1,'4th Floor, 20/1, New Palasia' addr2,'Indore' addr3 FROM dual UNION ALL
SELECT 'Shop No. 1,Vishnu Priya Building,' addr1,'' addr2,'pune' addr3 FROM dual UNION ALL
SELECT 'D/7, Siddhivinayak Nagari,' addr1,'Nr. Majura Gate, Ghod dod Road,' addr2,'' addr3 FROM dual UNION ALL
SELECT '' addr1,'B 4, Gold Coin Complex,' addr2,'Ahmedabad' addr3 FROM dual UNION ALL
SELECT '' addr1,'' addr2,'' addr3 FROM dual)
select addr1||'|'||addr2||'|'||addr3 address from address;
View 18 Replies
ADVERTISEMENT
Feb 23, 2013
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
View 3 Replies
View Related
Oct 23, 2009
Been trying to concatenate the following two select statements (to then pass as a parameter in DML statement) without much luck.
Select lpad(ST_DAY,'2',0) || '/' || lpad(ST_MONTH,'2',0) from RET_FORMATS;
Select to_char(sysdate,'YYYY')"Year" FROM DUAL;
View 1 Replies
View Related
Aug 30, 2007
My scenario is something like this:
MyTable
Rownum Colval
1 23
2 34
3 45
I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?
View 2 Replies
View Related
May 3, 2011
I'm having some troubles while concatenating fields.
If I make a simple join it works right:
select t1.nombre_archivo, t2.periodo
from proc_lotes t1, proc_procesos t2
where t1.lote_id = t2.lote_id;
NOMBRE_ARCHIVO PERIODO
------------------------------ ------------------------------
OSDE 201101
OSDE 201102
IOMA 201101
IOMA 201102
PAMI 201101
PAMI 201102
But... when I try to concatenate both fields it doesn't work. Nombre_archivo and periodo data type is varchar(30)
select t1.nombre_archivo + '_' + t2.periodo
from proc_lotes t1, proc_procesos t2
where t1.lote_id = t2.lote_id;
Error starting at line 1 in command:
select t1.nombre_archivo + '_' + t2.periodo
from proc_lotes t1, proc_procesos t2
where t1.lote_id = t2.lote_id
Error report:
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
PS. I'm using SQLDeveloper 1.5.3
View 4 Replies
View Related
Sep 21, 2011
I have two tables
Tab1 columns c1,c2,c3 data as
t1r1c1,t1r1c2,t1r1c3
t1r2c1,t1r2c2,t1r2c3
t1r3c1,t1r3c2,t1r3c3
Tab2 columns c1,c2,c4 data as
t1r1c1,t1r1c2,t2r1c3
i want to get results like below
t1r1c1,t1r1c2,t1r1c3_t1r2c3_t1r3c3
join columns will be c1 and c2 columns.
View 2 Replies
View Related
Jan 25, 2012
I have a field (called Date_Time) which displays for example 1/31/2005 12:00:00 AM. I would like to run a query that converts that value to '200501' in a created field.
I tried the following below but I keep having problems.
select Account_Number, Date_Time,
concat(year(Date_Time), month(Date_Time)) as Date_Time_Modified
from table
where Account_Number = xxxx
View 5 Replies
View Related
Sep 11, 2013
I'm Trying to use Listagg function in oracle 11g for concatenating values from different rows,but i'm getting error as FROM KEYWORD NOT FOUND.
Query is:
select listagg(column_name,'') within group (order by column_name) "column_name"
from table_name;
View 2 Replies
View Related
Aug 21, 2012
I want to update the names of employees by concatenating with A for DEPT 20. For that I have written the following PL/SQL block. But instead of one A the ename are concatenating with five AAAAA s.
DECLARE
TYPE lt_emp_arr IS TABLE OF t_emp.deptno%TYPE;
l_emp_arr lt_emp_arr;
BEGIN
SELECT deptno
BULK COLLECT INTO l_emp_arr
[code].......
I got the following out put.
EMPNOENAME SAL DEPTNO
7566JONESAAAAA200020
7788SCOTTAAAAA300020
7902FORDAAAAA300020
7369SMITHAAAAA800020
7876ADAMSAAAAA 130020
But I want the output like this
EMPNOENAMESAL DEPTNO
7566JONESA200020
7788SCOTTA300020
7902FORDA300020
7369SMITHA800020
7876ADAMSA 130020
View 1 Replies
View Related
Aug 21, 2012
I want to update the names of employees by concatnating with A for DEPT 20. For that I have writteh the following PL/SQL block. But instead of one A the ename are concatinating with five AAAAA s.
DECLARE
TYPE lt_emp_arr IS TABLE OF t_emp.deptno%TYPE;
l_emp_arr lt_emp_arr;
BEGIN
SELECT deptno
[Code]...
/I got the following out put.
EMPNO ENAME SAL DEPTNO
7566 JONESAAAAA 2000 20
7788 SCOTTAAAAA 3000 20
7902 FORDAAAAA 3000 20
7369 SMITHAAAAA 8000 20
7876 ADAMSAAAAA 1300 20
But I want the output like this
EMPNO ENAME SAL DEPTNO
7566 JONESA 2000 20
7788 SCOTTA 3000 20
7902 FORDA 3000 20
7369 SMITHA 8000 20
7876 ADAMSA 1300 20
how to achieve this.
View 9 Replies
View Related
Jun 26, 2013
I want to do 'ORDER BY' by concatenating the two columns(date +varchar2).But not working and geting the error - ORA-01855: AM/A.M. or PM/P.M. required
SQL> create table dat2 (
mod_date date,
am_pm varchar2 (10) ) ; 2 3
Table created.
SQL> INSERT INTO DAT2 ( MOD_DATE, AM_PM ) VALUES (
TO_Date( '06/05/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '7:25AM'); 2
1 row created.
[code]...
ERROR at line 2:
ORA-01855: AM/A.M. or PM/P.M. required
View 25 Replies
View Related
May 20, 2011
I'd like to load ss_numbers, but concatenate dashes in between.
The ssn currently looks like this in the file: 123456789
I'd like to put dashes in there to make it load like this. 123-45-6789
The data is positional, so I have the column in the ctl file defined as:
ssn position(1:9) char nullif ssn=blanks ,
I know how to concat using the other method of loading this way, but this is not how the data file looks:
"substr(lpad(:ssn,9,'0'),1,3)||'-'||substr(lpad(:ssn,9,'0'),4,2) ||'-'||substr(lpad(:ssn,9,'0'),6,4)",
how to get the dashes in there while loading with the positional method?
View 4 Replies
View Related
Nov 28, 2012
column1 column2 column3 column4
12 Mar-21-2005 BDW blah blah blah
11 Feb-07-2001 ZV ha ha ha
12 Jan-02-2002 YM zuck zuck zuckI want a view that has that data like this:
column1 column2
12 Mar-21-2005 - BDW - blah blah blah; Jan-02-2002 - YM - zuck zuck zuck
11 Feb-07-2001 ZV ha ha haCan you help with SQL ?
I tried to use this Oracle LISTAGG function in the SQL, but got a "string concatenation limit exceeded"
View 3 Replies
View Related
Feb 17, 2011
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
View 10 Replies
View Related
Dec 27, 2011
I 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 ?
View 9 Replies
View Related
Dec 31, 2012
How to get ip address in forms 6i ?
View 6 Replies
View Related
Jan 25, 2012
I intend to write procedures/functions in PL/SQL to parse Name field into various fields : Title, FirstName, MiddleName, LastName, Gender.
E.g:
Nguyen Van A
Nguyen Thi B
After parsing, the result will be shown as:
TitleFirstNameMiddleNameLastNameGender
MrNguyenVanAMale
MsNguyenThiBFemale
Parsed.gif ( 4.94K )
Number of downloads: 2
I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender = "F". Otherwise, Title = "Mr", and Gender = "M".
2/ Another procedure/function is [i]ParseAddress with the requirement as:[/i]Address field is divided into Street, Group, Area, Ward, County fields
E.g.:No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London
The result:
StreetGroupArea Ward County
No 6 Sum StreetGroup 8Area 2ABCD London
I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->it doesn't work.
View 1 Replies
View Related
Jan 25, 2012
I intend to write procedures/functions in PL/SQL to parse Name
field into various fields : Title, FirstName, MiddleName, LastName, Gender
E.g:
Nguyen Van A
Nguyen Thi B
After parsing, the result will be shown as:
TitleFirstNameMiddleNameLastNameGender
MrNguyenVanAMale
MsNguyenThiBFemale
I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender =
Female. Otherwise, Title = "Mr", and Gender = "Male".
2/ Another procedure/function is ParseAddress with the
requirement as:
Address field is divided into Street, Group, Area, Ward, County fields
E.g.:
No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London
The result:
StreetGroupAreaWardCounty No 6 Sum StreetGroup 8Area 2ABCDLondon
I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->
it doesn't work.
View 3 Replies
View Related
Nov 6, 2007
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?
View 2 Replies
View Related
Feb 15, 2010
method to get the ip address of Client machine using oracle forms .
I am using Oracle forms 6i and application server 9i.
View 9 Replies
View Related
May 2, 2011
how i can get Client IP address using Forms 10g Rel.2 and if other useful information i can get, how is it possible? is there any other solution if not using WebUtils ?
View 2 Replies
View Related
Jun 13, 2012
I'm trying to get client IP Address and show it to the user. I'm using forms 10g, with application server under UNIX.
View 1 Replies
View Related
Sep 26, 2011
we can find the IP address in V$session table. Is it possible to find the mac address of user's computer?
View 13 Replies
View Related
Jan 26, 2012
The title for this new topic is self explanatory. Since PostgreSQL has data types for storing IPV4 and MAC address and Oracle don't, I would like to know the right way to store this values so we can do different operations and validations with these network address.
I been doing some research but I don't like the solutions like storing just number values whit out any validations like PostgreSQL.
View 4 Replies
View Related
Feb 18, 2007
Has any one written the validation code for
IP Address...
i.e it should be any thing from 0.0.0.0 to 255.255.255.255
View 6 Replies
View Related
Apr 16, 2010
I want to write a SQL statement to search valid city name from address field. Valid city names are in one table and address column is in another table.
View 5 Replies
View Related
Mar 22, 2013
I am running 11.2. Within a DB trigger, I need to capture the IP address of the client making the change. I see there is a SYS_CONTEXT('USERENV','IP_ADDRESS') built in. Is this the correct way to capture the IP of the client making the change?
View 2 Replies
View Related
Apr 17, 2013
I'm using Oracle 9i. I want to make an address: city, state zip.
However, if there is no city or state, I don't want the comma. Doing this in decode made my head swim, so I went to case. I think that I mapped it out well, but I cannot have the variable on each section. This is my currently incorrect code.
CASE WHEN LENGTH(TRIM(AG.CITY)) = 0
THEN ST1.CODE || ' ' || AG.POSTAL_CODE
ELSE WHEN LENGTH(TRIM(ST1.CODE)) = 0
THEN AG.CITY || ' ' || AG.POSTAL_CODE
ELSE AG.CITY || ', ' || ST1.CODE || ' ' || AG.POSTAL_CODE
END ATTY_CITY_STATE_ZIPI'm trying to stick the end-product into ATTY_CITY_STATE_ZIP.
View 4 Replies
View Related
Jul 9, 2013
I have got a table where the site address is stored as follows -
SOUTH PARK ROAD #822,,,,
HOLLYWOOD,FL,33021,,
USPO BOX 1111,,,,
PHILADELPHIA,PA,19178-3478,,
USNORTH AVE EAST,
P.O. BOX 1234,,,ELIZABETH,NJ,07207-6031,,
US12345 PARK SORRENTO SUITE 222,,,,
CALABASAS,CA,91302,,US
I have got 2 fields to be populated from this - CITYSTATE How can I pick up the 5th and 6th field separated by the comma.
For example: -
Following output should be shown for the above recordsCITY
STATEHOLLYWOOD FLPHILADELPHIA PAELIZABETH NJCALABASAS CA
View 3 Replies
View Related
Nov 30, 2012
in listener.ora, if i use localhost, or computer name, then everything is ok, but when i try to use ip address, then I got a error ORA-12541: TNS:no listener
the listener.ora file which working well
# listener.ora Network Configuration File: C:oracleproduct10.2.0db_1NETWORKADMINlistener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:oracleproduct10.2.0db_1)
(PROGRAM = extproc)
[code]....
the only change is use the ipaddress of the server 10.183.7.89 instead of localhost, and if I change the ip address to the computer name, then its also working well.
The server is running on visual box, i don't know if the vistual machine cause this problem, I just want to confirm: I can use the ip address directly right?
View 21 Replies
View Related