13 Replies - 364 Views - Last Post: 17 April 2013 - 04:02 PM Rate Topic: -----

#1 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 215
  • Joined: 25-April 11

query words in strings

Posted 16 April 2013 - 02:30 AM

Hello

I'm not so strong in SQL, so I was wondering if there is a possibility to query a string for specific words, which are not necessarily separated by a space but sometimes by special chars too.

For example
Row1: "This could be a string where the Word -SAR-is contained but not as a normal word separated by spaces"
Row2: "This instead is a string which contains the name of a girl called Sarah"
Row3; "This is a normal string where SAR is just a word"

Now I would like to select the rows, where SAR is contained (not important if uppercase or lowercase) as a word and not as part of a word like the name SARah. In my example they would be row1 and row3.

I wanted to use Regexp, but I realized that it is not implemented in SQLITE3 by default. How would you aproach that?

Until now I tried something like that, but it does not properly work:
WHERE 
(`myField` LIKE '% SAR %' OR 
`myField` LIKE '% SAR_' OR 
`myField` LIKE '% SAR_ %' OR 
`myField` LIKE '% SAR' OR 
`myField` LIKE '%-SAR %' OR 
`myField` LIKE '%-SAR_' OR 
`myField` LIKE '%-SAR_ %' OR 
`myField` LIKE '%-SAR');



Is This A Good Question/Topic? 0
  • +

Replies To: query words in strings

#2 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: query words in strings

Posted 16 April 2013 - 03:19 AM

You can actually look for spaces using the LIKE operator with the * wildcard character.

i think the below one should work.(NOT TESTED JUST GUESSING)

WHERE (myfield LIKE "* * *" and myfield like '%s_r%'


HERE IS A LINK, I HOPE U CAN GET SOMETHING USEFULL
My link

This post has been edited by kai_itz me: 16 April 2013 - 03:21 AM

Was This Post Helpful? 0
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3635
  • View blog
  • Posts: 5,756
  • Joined: 08-June 10

Re: query words in strings

Posted 16 April 2013 - 03:22 AM

View PostAnthonidas, on 16 April 2013 - 09:30 AM, said:

I wanted to use Regexp, but I realized that it is not implemented in SQLITE3 by default. How would you aproach that?

Depends on the language you are using to execute the SQL queries.

In PHP, I would just define the "regexp" function first, and then use the REGEXP operator to look for the keyword using regexp word boundaries. - For instance, after creating a PDO object to connect to a SQLite3 DB, I would execute something like this: (The SQLite3 API also has a similar function [ref].)
$pdo->sqliteCreateFunction('regexp', function($pattern, $testString) {
    return (bool) preg_match($pattern, $testString);
}, 2);


And from that point on you should be able to just do:
$result = $pdo->query("SELECT stuff FROM thetable WHERE myField REGEXP '/\\bSAR\\b/'");


Was This Post Helpful? 2
  • +
  • -

#4 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 215
  • Joined: 25-April 11

Re: query words in strings

Posted 16 April 2013 - 03:31 AM

kai_itz me, thank you very much. I will check your link and post back.

Atli, I'm using C#. So I could define the same method by pointing the REGEXP to PHP's equivalent Regex.IsMatch, couldn't I?
Was This Post Helpful? 0
  • +
  • -

#5 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3635
  • View blog
  • Posts: 5,756
  • Joined: 08-June 10

Re: query words in strings

Posted 16 April 2013 - 03:33 AM

View Postkai_itz me, on 16 April 2013 - 10:19 AM, said:

You can actually look for spaces using the LIKE operator with the * wildcard character.

i think the below one should work.(NOT TESTED JUST GUESSING)

WHERE (myfield LIKE "* * *" and myfield like '%s_r%'


HERE IS A LINK, I HOPE U CAN GET SOMETHING USEFULL
My link

That's not valid for SQLite, or any of the major SQL databases either. It seems this is an Access specific thing. (Which the article in that link is teaching.)

See the proper use of wild-cards for the LIKE operator in SQLite in the SQLite docs. The equivalent for what you are doing would be: myfield LIKE '% % %', although I don't really see the use in this situation. It only checks for any three character sequences separate by spaces.
Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3635
  • View blog
  • Posts: 5,756
  • Joined: 08-June 10

Re: query words in strings

Posted 16 April 2013 - 03:39 AM

View PostAnthonidas, on 16 April 2013 - 10:31 AM, said:

Atli, I'm using C#. So I could define the same method by pointing the REGEXP to PHP's equivalent Regex.IsMatch, couldn't I?

How are you connecting to SQLite in C#? I don't know that much about how you'd use SQLite with that, but if it provides a way to create user defined functions, similar to how PDO::sqliteCreateFunction works in PHP, then you should be able to do the same thing there.

Edit:
This question on SO might interest you, if you are using the system.Data.SQLite API.

This post has been edited by Atli: 16 April 2013 - 03:45 AM

Was This Post Helpful? 1
  • +
  • -

#7 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: query words in strings

Posted 16 April 2013 - 03:43 AM

i don't even had idea about SQLite. I was just making a guess.
thanks for proper guidance moderator..
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2884
  • View blog
  • Posts: 9,566
  • Joined: 12-December 12

Re: query words in strings

Posted 16 April 2013 - 03:46 AM

I think the GLOB operator in SQLite switches the wildcard characters from % and _ to * and ?, and also makes the comparisons case sensitive, but this offers no value for the current question.

BTW The reason the original code wasn't working was because of the use of OR rather than AND.
Was This Post Helpful? 0
  • +
  • -

#9 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 215
  • Joined: 25-April 11

Re: query words in strings

Posted 16 April 2013 - 05:15 AM

Atli, exactly, I am using the system.Data.SQLite API. Ok I'll create this function this evening and try to pass a regex to see if it works as I would like to.

andrewsw why do you think that? supposed I would use AND's rather than OR's in the first example, I would get none of the rows... because none of the rows match every statement.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2884
  • View blog
  • Posts: 9,566
  • Joined: 12-December 12

Re: query words in strings

Posted 16 April 2013 - 05:59 AM

View PostAnthonidas, on 16 April 2013 - 12:15 PM, said:

andrewsw why do you think that? supposed I would use AND's rather than OR's in the first example, I would get none of the rows... because none of the rows match every statement.

The statement using OR in your first post is going to bring in a lot of combinations of SAR, including the ones that you don't want to show. I didn't mean to just replace all of them with ANDs, but more like:

WHERE `myField` LIKE '%SAR%' AND (`myField` LIKE '% SAR %' OR `myField` LIKE '% SAR-%');

but this will get messy very quickly, and the regex-function solution will be much better. My apologies though, as this isn't that different to your original statement :)

I suppose another possible solution is to retrieve all the rows containing SAR (if the table isn't too huge!) and then filter the rows in C#.

This post has been edited by andrewsw: 16 April 2013 - 06:01 AM

Was This Post Helpful? 1
  • +
  • -

#11 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 215
  • Joined: 25-April 11

Re: query words in strings

Posted 16 April 2013 - 07:35 AM

View Postandrewsw, on 16 April 2013 - 02:59 PM, said:

I suppose another possible solution is to retrieve all the rows containing SAR (if the table isn't too huge!) and then filter the rows in C#.


Actually yes, it would be a good solution... but "SAR" was just an example to show what I meant, and I am pretty sure that there will be really "inteligent" people launching queries with only one or two letters. In those cases the result would be really too huge to handle in a fast way within C#.

Anyway thank you for your explanations.
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2884
  • View blog
  • Posts: 9,566
  • Joined: 12-December 12

Re: query words in strings

Posted 16 April 2013 - 07:38 AM

Yes, they can't be that intelligent :) if they are performing searches using only one or two letters. I would probably consider not performing a search on less than three characters.
Was This Post Helpful? 0
  • +
  • -

#13 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 215
  • Joined: 25-April 11

Re: query words in strings

Posted 17 April 2013 - 02:23 AM

ok i implemented the function you adviced and I had no problems compiling.

Now before I can test it, I should be sure, that my regex is correct. What do you experts think about this one, is it correct in your opinion: /(^|\b|[^a-zA-Z])(sar)(\b|[^a-zA-Z]|$)/i ?
I tested different expressions on this website ( http://rubular.com/ ) and the above expression is the one that gave me the best result. Just for testing purposes I used a string like that, and I got all the matches I wanted to.

Quote

sar sar -sar- sarah sara s-ar -_sar- SAR -SAR _SAR SAR_ sar ap-sar sar transaktionsart sar\n malisar sar
mar -tar -car csar- |sar bar

This post has been edited by Anthonidas: 17 April 2013 - 02:44 AM

Was This Post Helpful? 0
  • +
  • -

#14 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 215
  • Joined: 25-April 11

Re: query words in strings

Posted 17 April 2013 - 04:02 PM

got it...

this one works perfectly: /\b[^a-zA-Z]?[_]?(sar)[_]?[^a-zA-Z]?\b/i

This post has been edited by Anthonidas: 18 April 2013 - 01:07 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1