?
Solved

how do i use substr to do the format for an isbn 9-999-99999-9

Posted on 2009-02-14
11
Medium Priority
?
662 Views
Last Modified: 2012-05-06
:
how do i use substr to do the format for an isbn 9-999-99999-9Bookmark:
 

i tried the following:
SELECT SUBSTR(ISBN, 1,1)
FROM BOOKS;
this brings up a quiery but it does not format the isbn numer correctly
0
Comment
Question by:brantek
  • 5
  • 4
  • 2
11 Comments
 

Author Comment

by:brantek
ID: 23643742
I will need some assistance right away

thanks
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23643754
What it the format you have in your table and what is your expected format?
0
 

Expert Comment

by:Prasenjit_Dutta
ID: 23643756
Plz also specify the datatype ...
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

Author Comment

by:brantek
ID: 23643759

ISBN       TITLE                          PUBDATE        PUBID       COST
---------- ------------------------------ --------- ---------- ----------
    RETAIL CATEGORY
---------- ------------
0299282519 THE WOK WAY TO COOK            11-SEP-00          4         19
     28.75 COOKING

8117949391 BIG BEAR AND LITTLE DOVE       08-NOV-01          5       5.32
      8.95 CHILDREN

0132149871 HOW TO GET FASTER PIZZA        11-NOV-02          4      17.85
     29.95 SELF HELP


THE FORMAT SHOULD LOOK LIKE THIS:
 Format the ISBN code to display as 9-999-99999-9 (e.g., 1059831198 would display as 1-059-83119-8)


0
 

Author Comment

by:brantek
ID: 23643776
THE SELECT STATEMENT I WAS USING WAS THE FOLLOWING:

SELECT SUBSTR(ISBN,1,1)
FROM BOOKS;


BUT IT DOES NOT GIVE THE CORRECT FORMAT FOR THE ISBN.
0
 

Expert Comment

by:Prasenjit_Dutta
ID: 23643834
Try this...

Select substring(cast(isbn as varchar(10)),1,1)+'-'+substring(cast(isbn as varchar(10)),2,3)+'-'+
substring(cast(isbn as varchar(10)),5,5)+'-'+substring(cast(isbn as varchar(10)),10,1)  from test

-- Here test is the table name.  Assuming ISBN column is of int type, it has been converted to string...
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23643861
what is your SQL database?
0
 

Author Comment

by:brantek
ID: 23643864
it gave me the following error


ERROR at line 2:
ORA-00904: "SUBSTRING": invalid identifier
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 23643875
check this.
select concat(substr(ISBN,1,1),'-',substr(ISBN,2,3),'-',substr(ISBN,5,5),'-',substr(ISBN,10,1))
  from BOOKS;

Open in new window

0
 

Author Closing Comment

by:brantek
ID: 31551385
yes this answer worked the best for me.

thank you very much!!
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23740183
you are welcome :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses

601 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