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 '% %% %% %'
mahmud ahsan

Love computer programming and write occasional blog post. In my early career I worked mostly on web based application and since 2011, I exclusively working on iPhone and android applications. I work at home, father of a nice kid and husband of a beautiful wife. [My Business | Twitter | Linkedin]

You may also like

1 Comment

  • 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… 😉

Comments are closed here.

About Me

Hi I am Mahmud Ahsan. I Love computer programming and write occasional blog post. In my early career I worked mostly on web based application and since 2011, I exclusively working on iPhone and android applications. I work at home, father of a nice kid and husband of a beautiful wife. [My Business | Twitter | Linkedin]

Twitter Feed

Fanpage