0

I would like to perform a "on the fly" modification of a field of a sqlite database, before doing a SELECT statement.

The field is called 'topic_simple', and it contains some text. Somewhere in the text, there is a kind of separator I included: 4 spaces: " ".

Here is one of my typical SELECT statement:

SELECT * FROM papers WHERE (topic_simple LIKE '% 3D print%')

I would like something like that:

SELECT * FROM papers WHERE ((topic_simple_before_4_spaces) LIKE '% 3D print%')

I DO NOT WANT to modify topic_simple in the database, I just want to make a select statement on a substring of this field.

How can I do that ?

JPFrancoia
  • 4,866
  • 10
  • 43
  • 73
  • seriously suggest improving your storage structure. **[this post](http://stackoverflow.com/questions/34553775/mysql-select-query-to-fetch-record-base-on-list-values/34553991#34553991)** though gives a solution ( either in place separator processing or fixing it.. you will have to adapt ( maybe use a replace ) to convert multiple space separator to those solutions which work on single character delimiters – amdixon Jan 04 '16 at 09:43

1 Answers1

0

LIKE is quite powerful. Why don't you just add the four spaces to your LIKE expression:

SELECT *
FROM   papers
WHERE  topic_simple LIKE '% 3D print%    %';

Be aware that the text after your delimiter shouldn't contain the delimiter because then you'd possibly get unwanted lines. If your SQLite supports it, you can also use a regular expression.

In MYSQL you can directly "select" (or "extract") the text before your four spaces in the WHERE clause:

SELECT *
FROM   papers
WHERE  SUBSTRING(topic_simple, 1, LOCATE('    ', topic_simple) - 1) ...;
steffen
  • 16,138
  • 4
  • 42
  • 81
  • There is no substring function in sqlite. There is a substr one though. But what about locate ? – JPFrancoia Jan 04 '16 at 10:02
  • Nope sorry, but the LIKE query you suggest won't return what I want. I used ```'% 3D print%'``` which would returns entries containing for example '3D printer' or '3D printing'. I just want to perform my like query on the part before the 4 spaces. – JPFrancoia Jan 04 '16 at 10:25
  • @Rififi I forgot one `%`. You can use your `LIKE` as you »like«, but you need to append the four spaces and `%` to it. (see edit) – steffen Jan 04 '16 at 10:28
  • Nope, no way it could work in that case. As it was too complicated,and probably overkill, I just modified the structure of my database. I added one field, but probably improved the perf of my sql query. What should I do about this post ? – JPFrancoia Jan 05 '16 at 15:40
  • @Rififi That's probably best. Well, adding spaces and `%` would work as well, at least that's what I tested here. You could write your own answer and accept it. Or accept mine. Whatever. – steffen Jan 05 '16 at 15:41