CODEWITHSUNDEEP
sqloracle-databasesubstr
jwebster
jwebster

Reputation: 648

Oracle SQL substr

I have two tables:

Employee(empNo, lName, fName, sex, dob, address, deptno, position)
Zip(city, state, zipcode)

In the Employee table, the address is stored like this: 55 Maple Ln., AL 55555

Yes, I know this is the most horrific way to store an address ever, but this is out of my control.

I need to return empNo, lName, fName, the street portion of the address, the state portion, the city, and the zipcode in a query. This is what I am using and I am having a little difficulty getting it to work properly:

SELECT empNo, lName, fName,
    substr(Address,0, INSTR(Address,',')-1) AS streetAddr,
    city,
    substr(Address, INSTR(Address,',')+2,2) AS stateAddr,
    substr(Address,-5) AS zipC
FROM Employee
    JOIN Zip z ON z.zipcode = zipC
WHERE deptNo = '124';

The error I am getting is on the JOIN statement; zipC is an invalid identifier. My problem is that I am trying to JOIN Employee and Zip via the zipcode portion of the tables. I only need to do this so that I can get city out of zipcode.

EDIT #1

I have also tried this:

SELECT e.empNo, e.lName, e.fName,
    substr(e.Address,0, INSTR(e.Address,',')-1) AS streetAddr,
    z.city,
    substr(e.Address, INSTR(e.Address,',')+2,2) AS stateAddr,
    substr(e.Address,-5) AS zipC
FROM Employee e, Zip z
WHERE deptNo = '124';

Upvotes: 0

Views: 1923

Answers (1)

shonky linux user
shonky linux user

Reputation: 6428

SELECT empNo, lName, fName,
    substr(Address,0, INSTR(Address,',')-1) AS streetAddr,
    city,
    substr(Address, INSTR(Address,',')+2,2) AS stateAddr,
    substr(Address,-5) AS zipC
FROM Employee
    JOIN Zip z ON z.zipcode = substr(Address,-5)
WHERE deptNo = '124';

i.e. do not use the alias in the join.

If there is any danger of leading or trailing spaces you may also want to trim() your address values before determining the INSTR or substr

Upvotes: 2

Related Questions