substring extraction in Mysql

Question

I have some ISBN data in the field description, the data is formatted like so ISBN:10 (ten digit ISBN number) , the string in the field description is not patterned, for example, it can appear like this

(author name text) (custom name text) ISBN:10 (isbn number) (some other data text) or also like this with just the isbn number ISBN:10 (isbn number) and so on.

I have tried the following LOCATE mysql statement, but this turns out irregular data as it removes some numbers from the ISBN number

SELECT
    SUBSTRING(database.description, 
      LOCATE('ISBN10:', auctions.description)+8) 

    AS ISBn_number
FROM 
    database_books AS database

Can you post an example row where it removes some numbers? Which numbers (beginning, end?) Is there always a space after ISBN10:? — kielni
– kielni, Commented Sep 11, 2013 at 17:06

Kenyakorn Ketsombut · Accepted Answer · 2014-02-25 04:42:36Z

Assuming that each of your isbns is really included in the description in the format

ISBN:10<space>0123456789<space>

and you can select all the dataset with the where clause

where description like '%ISBN:10 __________ %'

then you can extract all the ISBNs with:

select SUBSTRING(description,LOCATE('ISBN:10 ',description)+8,10 ) isbn10 
from database_books
where description like '%ISBN:10 __________ %'

Note1: It appears that you try to select from 2 different tables 'database' and 'auctions'.
Note2: I wouldn't recommend to call a table 'database'.
Note3: I wouldn't recommend that you create an auction house software ;) (joke)

Collectives™ on Stack Overflow

substring extraction in Mysql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related