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

Programming is my passion from high school. I always looking for innovating new ideas and making them real application. I love mobile application and game development as well as web development. Having 4+ years web development experience and 6+ years mobile application development experience on different platform and business on it. My Mobile Apps Portfolio: iThinkdiff.net Mobile Apps

, , , , ,

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… ;)