• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 742
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

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.

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