?
Solved

SQL-zip-code split

Posted on 2009-05-08
9
Medium Priority
?
729 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 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

752 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