Solved

SQL-zip-code split

Posted on 2009-05-08
9
715 Views
Last Modified: 2013-12-07
I have the following (hopefully simple) SQL question.  I have a field which holds the zip code in ZIP5-ZIP4 format.  I would like to Select this field into TWO fields (one for ZIP5 and one for ZIP4).  The two ZIP fields have a "-" between them.  How do I do this?
Thanks
0
Comment
Question by:mbroad02
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24336795
SELECT LEFT(Zip, (CharINDEX('-',Zip))-1 ),
       SUBSTRING(Zip, CharINDEX('-',Zip)+1  ,LEN(Zip) )
0
 

Author Comment

by:mbroad02
ID: 24336831
How do I place this code in the existing SQL ?

select ZIP_CD, JSDN_CD from JURYDBA.ZIPCDWHERE JSDN_CD  <> 'NMAC'
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24336851
SELECT LEFT(Zip, (CharINDEX('-',Zip))-1 )  Zip1,
                SUBSTRING(Zip, CharINDEX('-',Zip)+1  ,LEN(Zip) ) Zip2 , JSDN_CD from JURYDBA.ZIPCD
WHERE JSDN_CD  <> 'NMAC'
         
 
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:mbroad02
ID: 24336966
I know this is just a syntax problem, but I get the following error:

ORA-00904: "SUBSTRING": invalid identifier

When I use the syntax you list above....  ???
0
 

Author Comment

by:mbroad02
ID: 24336974
By the way, I am using a SQL tool called Benthik-Golden to extract from this Oracle database.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24337026
mbroad02,

The above syntax is for SQL Server 2008, you put that zone too in your question, i hope some one else from Oracle zone will help you

Aneesh
0
 

Author Comment

by:mbroad02
ID: 24337117
Thanks.  I asked the question for Oracle zone and I will close this question and give you the points.  thank you very much.
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24337170
SUBSTRING isn't an Oracle function.  
The Oracle syntax would be:
select substr(zips, 0, instr(zips,'-') - 1) zip5,
           substr(zips, instr(zips,'-') + 1) zip4
from t
SQL> create table t(zips varchar2(512));
Table created.
SQL> insert into t values ('3434-21444');
1 row created.
SQL> insert into t values ('956-1112');
1 row created.
SQL> select substr(zips, 0, instr(zips,'-') - 1) zip5,
  2         substr(zips, instr(zips,'-') + 1) zip4
  3  from t;
 
ZIP5            ZIP4
--------------- ---------------
3434            21444
956             1112
SQL>

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 24337648
Since you included the Crystal zone you could have used a formula
Split({ZipField},'-')[1]   - Zip
Split({ZipField},'-')[1]   - Zip+4

mlmcc
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question