• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

SQL-zip-code split

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
mbroad02
Asked:
mbroad02
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT LEFT(Zip, (CharINDEX('-',Zip))-1 ),
       SUBSTRING(Zip, CharINDEX('-',Zip)+1  ,LEN(Zip) )
0
 
mbroad02Author Commented:
How do I place this code in the existing SQL ?

select ZIP_CD, JSDN_CD from JURYDBA.ZIPCDWHERE JSDN_CD  <> 'NMAC'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
mbroad02Author Commented:
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
 
mbroad02Author Commented:
By the way, I am using a SQL tool called Benthik-Golden to extract from this Oracle database.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
mbroad02Author Commented:
Thanks.  I asked the question for Oracle zone and I will close this question and give you the points.  thank you very much.
0
 
AndytwCommented:
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
 
mlmccCommented:
Since you included the Crystal zone you could have used a formula
Split({ZipField},'-')[1]   - Zip
Split({ZipField},'-')[1]   - Zip+4

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now