Returning a small extract where column type = text

Is it possible to only show ten or so characters before and after a matched phrase? I’m not interested in all the text in the column being returned, as sometimes it’s multiple paragraphs long.

where exampleColumn like '%exact phrase here%'

Thank you!

[quote=“busboy, post:1, topic:194582, full:true”]
Is it possible to only show ten or so characters before and after a matched phrase? [/quote]yes indeed, although it is approximately an order of magnitude easier to do it in your application language (php or whatever)

This is something I am trying to take care of inside the mySQL app that I use, Querious. In this case, I will be editing data right in the app, as opposed to integrating it with a web front-end.


okay, i’ll explain briefly how to go about it – it involves using nested string functions

first of all, the WHERE clause ensures that all rows returned contain the exact phrase somewhere inside the column value, so you can use INSTR() to find the position where that phrase starts

then use some math to subtract 10 (or however many characters you want in front), but you also have to make sure you don’t end up with a negative number, so you can use GREATEST() function to pick the result of the subtraction or 1, whichever is greater

then just use the SUBSTR() function to extract what you want, with the math calculation just mentioned as the starting position, and anopther calculation for the length of the substring (no need to check for going past the end of the column value, since SUBSTR() will only go up to the end anyway)

simple, if you approach it methodically

1 Like

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