MYSQL query help- Substring and Instring

Question

I need help writing a query to get some data from a table. I am trying to write a query that will select all of the book titles that have “bill” in their name and will display the title of the book, the length of the title, and the part of the title that follows “bill”. I know you are supposed to use the substring and instring functions but i keep on running into syntax errors and/or incorrect output

The book table is as follows

CREATE TABLE Book( 
   ISBN        CHAR(13),
   Title       VARCHAR(70) NOT NULL,
   Description VARCHAR(100),
   Category    INT,
   Edition     CHAR(30),
   PublisherID INT         NOT NULL,
   constraint book_ISBN_pk PRIMARY KEY (ISBN),
   constraint book_category_fk FOREIGN KEY (Category) REFERENCES Category(CatID),
   constraint book_publisherID_fk FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID)
);

Does the part of the title that follows “bill” include "bill" itself? — KM11
– KM11, Commented May 10, 2015 at 20:14

dnoeth · Accepted Answer · 2015-05-10 19:46:11Z

1

This is a Standard SQL version, afaik mysql should support those functions, too:

select 
   Title
  ,char_length(Title)
  ,substring(Title from position('bill' in Title) + 4)
from book
where Title like '%bill%'

answered May 10, 2015 at 19:46

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hd1 · Accepted Answer · 2015-05-10 19:48:11Z

0

Use regexp for the match, as follows:

select title from books where title regexp '*book*'

As for what you're supposed to use, in my book, you're only supposed to use what gets the desired result. Optimisation comes later, if ever.

answered May 10, 2015 at 19:48

hd1

34.9k5 gold badges83 silver badges95 bronze badges

Comments

KM11 · Accepted Answer · 2015-05-10 20:00:23Z

0

Using SUBSTRING, LENGTH and INSTR

SELECT
  Title,
  LENGTH(Title) as Length,
  SUBSTRING(Title,INSTR(Title, 'bill'),LENGTH(Title)-INSTR(Title, 'bill')) as Rest
FROM
  Book
WHERE
  Title like '%bill%'

Tested - SQL Fiddle

answered May 10, 2015 at 20:00

KM11

7676 silver badges15 bronze badges

Collectives™ on Stack Overflow

MYSQL query help- Substring and Instring

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related