SQLite space within string

Today I was working in some SQLite3 tasks for one of our iPhone application. One of the problem I faced is that, I’ve to know the string data that contains a certain number of  ‘ ‘ space.  Suppose If I need to know the string data that has 2 space or may be 3 space then how can I do so.

The simplest thing I tried and that solved my problem by the following way:

if I one to search 1 space:

SELECT * FROM tbl_name where str_field LIKE '% %'

if I one to search 2 space:

SELECT * FROM tbl_name where str_field LIKE '% %% %'

if I one to search 3 space:

SELECT * FROM tbl_name where str_field LIKE '% %% %% %'

About mahmud ahsan

Founder And Lead Programmer at iThinkdiff.net

, , , , ,

One Response to SQLite space within string

  1. Benoit St-Jean January 6, 2013 at 6:30 am #

    There’s a more elegant and generic solution to this kind of problem! What would you do if you needed to search for strings with 19 spaces ?!?!?! Or if you needed to search for strings matching a certain substring multiple times ?

    I’m explaining the solution of finding the number of occurrences (in MySQL but it’ll work for SQLite as well) on my blog (sorry, it’s in French but I guess you’ll get it and Google Translate usually does a decent job of translating) here:

    http://endormitoire.wordpress.com/2012/03/04/comment-trouver-le-nombre-doccurrences-dune-chaine-de-caracteres-dans-mysql/

    Exercise to adapt it to your particular problem is left as an exercise to the reader… ;)