Solved

SQL-zip-code split

Posted on 2009-05-08
9
706 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
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 100

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

777 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