Extract multiple paragraphs from text string

Databases
1

With the help from Rudi I use the following query to extract the first and second paragraph from a longer text string:

SELECT SUBSTRING_INDEX(page_content,CHAR(13,10),1) AS first_paragraph
     , SUBSTRING_INDEX(
       SUBSTRING_INDEX(page_content,CHAR(13,10),2)
                                   ,CHAR(13,10),-1) AS second_paragraph
  FROM ...

That works really great. But if ,for example, I have a string with 6 or 8 paragraphs and I want to devide that in two text blocks of 3 or 4 paragraphs. How does the query than looks like?

Thank you in advance

2

SELECT SUBSTRING_INDEX(page_content,CHAR(13,10),1) AS first_paragraph , SUBSTRING_INDEX( SUBSTRING_INDEX(page_content,CHAR(13,10),2) ,CHAR(13,10),-1) AS second_paragraph , SUBSTRING_INDEX( SUBSTRING_INDEX(page_content,CHAR(13,10),3) ,CHAR(13,10),-1) AS third_paragraph FROM ...

3

Hi Rudi. Thanks for the reply. But what if I want two blocks, the first from the first three paragraphs and the second from the next three paragraphs should it look like this:

SELECT SUBSTRING_INDEX(page_content,CHAR(13,10),3) AS first_block
     , SUBSTRING_INDEX(
       SUBSTRING_INDEX(page_content,CHAR(13,10),6)
                                   ,CHAR(13,10),-1) AS second_block
  FROM ...
4

what happened when you tested it? ™

5

Hello Rudi. I just tried using the query as above and I tried to display the first block (three paragraphs):

<?= $travel_info['first_block']; ?>

It should just display the first 3 pragraphs but it displayed all paragraphs instead.

6

having a hard time believing that

are you sure there were more than 3 instances of CHAR(13,10)

7

Yes I do: This is coming straight from the database:

<p><span class="d-block">Air travel</span>If you book your tickets early enough, especially with EasyJet, you can get tickets for less than £100. Aegean airlines have also good prices. Many of our customers are using travel agencies again who are now as competitive as the Internet, but more convenient and faster.</p><p><span class="font-italic">Luggage safety:</span> If you have a stop on your flight, I suggest that you attach an extra contact sticker on your bags. I make them myself, stick my contact and flight number with tape on a plastic support that I attach with strong plastic link (rizlan, the one used by electricians).</p><p><span class="font-italic">Luggage cost:</span> Airlines now sometimes charge us for extra luggage. The best way in my opinion, if you are on a budget, is to cheat.</p><p><span class="d-block pb-5">Airport or train station transfers</span>You will be always picked up at the train station or at the airport if we have many customers, depending of arrival time and the number of pilots.</p><p>There is a direct train, right from the airport, to Korinthos (45 minutes from the hotel), where we will pick you up. This train is clean, on time and comfortable – all our customers appreciate it!</p><p><span class="d-block">Arrival time / departure time? </span>The last train from airport leaves at 10.39 pm, so if your flight is later than 9.30 pm, you may need an airport transfer (see above). The first train arrives at the airport at 7.06, so if your flight is earlier than 8.30 you will need an airport transfer (see above)</p><p>Sometimes we have a car left at the airport or at the train station by a returning customer. If you feel ok to drive it over to the hotel, inform us about it. The route is very simple and a map would, of course, be in the car.</p><p><span class="d-block">Travel Insurance</span>if you need a travel insurance that includes paragliding, check <a class="text-link" href="https://insurefortravel.co.uk/" title="Travel Insurance | Insure for Travel" target="_blank">Insure for Travel</a>.</p><p><span class="d-block">Car rental</span>Cars are not necessary, but some people like to be independent, here is a good address: <a href="http://www.economycarrentals.com/default.aspx?x02=645&resellerid=645" class="text-link" title="Economy Carrentals" target="_blank">EconomyCarRentals.com</a>.</p><p><span class="d-block">Staying overnight in Athens</span>If you would like to spend a night in Athens, we've found a nice hotel near Acropolis, which gives 15% discount to our customers. Simply mention, when booking a room, that you are a Flying Paradise’s customers. <a href=" https://www.panhotel.gr/en/" class="text-link" target="_blank" title="Pan Hotel Athens"> www.panhotel.gr</a>  Tel: +30 210 32 37 816 (or 817)</p>

Or I do something completely wrong?

8

looks like it

that particular block of text contains no CHAR(13,10) strings

9

Hi Rudi, so what are my options using just

<p></p>

combinations?

10

i think you should try using those in the SUBSTRING_INDEX function instead of CHAR(13,10)

11

So you are storing the strings of html code in the database, not plain text?

12

Hi Sam. Yes that is indeed the case. Should I avoid that?

13

no, that is perfectly okay :+1:

14

Hi Rudi, Thanks again for the reply. Excuse my ingnorance but how should I use the

<p></p>

combination within the Substring_Index?

15

Sounds good to me. I think LOCATE could come in handy for that

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_locate

16

see up thread, for examples of SUBSTRING_INDEX pulling out the first, second, third instance of a substring

it is really rather elegant

1 Like
17

replace CHAR(13,10) in the SUBSTRING_INDEX with those characters

18

Hi Rudi. Tried that as well, without success. If you can show me how I would be very greatful

This is what I got:

SELECT SUBSTRING_INDEX(page_content('<p>','</p>'),3) AS first_block
     , SUBSTRING_INDEX(
	   SUBSTRING_INDEX(page_content('<p>','</p>'),6)
							      ,('<p>','</p>'),-1) AS second_block
FROM

Ok get it working for the first block:

SELECT SUBSTRING_INDEX(page_content, '</p>', 3) AS first_block 
  FROM

But how to add a second, third and … block. I tried the same approach as with char(13, 10) but sofar without succes

19

Ok got it working:

SELECT page_content 
     , SUBSTRING_INDEX(page_content, '</p>', 3) AS first_block	
	 , SUBSTRING_INDEX(SUBSTRING_INDEX(page_content, '</p>', 6), '</p>' ,-1) AS second_block	                                 
  FROM

Thank you all for the input

1 Like
20

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.