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

SQL & PL/SQL :: Display Office Address / House Address And Emp ID In Single Query?

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

Concatenating Two Select Statements

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

Concatenating Result Of Query

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

SQL & PL/SQL :: Invalid Number While Concatenating?

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

SQL & PL/SQL :: Concatenating Two Rows While Joining

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

SQL & PL/SQL :: Concatenating Date And Time Field

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

Listagg Function In 11g For Concatenating Values From Different Rows

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

SQL & PL/SQL :: To Update The Names Of Employees By Concatenating With A For DEPT 20

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

PL/SQL :: Update Names Of Employees By Concatenating With A For DEPT 20

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

SQL & PL/SQL :: Date Format - Do (ORDER BY) By Concatenating Two Columns?

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

Server Utilities :: Concatenating Dashes In A Column Using Sqlldr

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

PL/SQL :: Concatenating Columns (in Multiple Rows) To One Column In Single Row

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

SQL & PL/SQL :: Dynamically Concatenating Column Values Based Upon Number Of Grouped Columns

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

SQL & PL/SQL :: How To Get Email Address From Xml

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

Forms :: How To Get IP Address In 6i

Dec 31, 2012

How to get ip address in forms 6i ?

View 6 Replies View Related

Parsing Address And Name

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

SQL & PL/SQL :: Parsing Name And Address?

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

Getting Name In Email Address (Oracle SQL Plus)

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

Forms :: How To Get Client IP Address

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

Forms :: Get Client IP Address Using 10g Rel.2?

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

Forms :: How To Get User IP Address

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

SQL & PL/SQL :: Mac Address Finding Using Oracle

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

SQL & PL/SQL :: Right Way To Store IPV4 And MAC Address

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

Forms :: IP Address Validation

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

SQL & PL/SQL :: Searching City Name From Address

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

Capture IP Address In DB Trigger?

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

PL/SQL :: Oracle 9i - How To Construct Address

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

PL/SQL :: Get CITY And STATE From Address

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

Use IP Address In Listener.ora Not Working

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







Copyrights 2005-15 www.BigResource.com, All rights reserved