Sign in to follow this  
Followers 0
gcue

StringRegExp expressed in _SQLite query

9 posts in this topic

#1 ·  Posted (edited)

hello.

i am trying to convert a StringRegExp query to a _SQLITE query.

essentially i am trying to match this string A to these scenarios (sql table values) where you can find string A

string A = "G283H88620TS"

matching sql table values

MX0G2H883H7426288620TS (contains the string but has characters before, in the middle, and at the end)

UIERG283H88620TSERU (contains the string but has characters before and at the end)

G283H88620TSKDJF (contains the string but has characters at the end

KEKEG283H88620TS (contains the string but has characters at the begining

unfortunately there is no pattern as to where these interrupting characters reside

(this function works-courtesy of martin)

Func _StringInStrRandom ($sSearchString, $sCharsString)
  Local $i, $Err = 0
  Local $aS=stringsplit($sCharsString,''),$newS
  
  for $i = 1 to $aS[0]
      $newS &= ".*" & $aS[$i]
  Next
   $newS &= ".*"
   ConsoleWrite($newS & @CRLF)

   return StringRegExp($sSearchString,"(?i:" & $newS & ")")
  
EndFunc; ==> _StringInStrRandom

(this only works for the last 3 scenarios but not the first)

"SELECT AssetTag,Model,SerialNumber FROM aInventoryInfo WHERE SerialNumber LIKE '%" & $serial & "%'"
Edited by gcue

Share this post


Link to post
Share on other sites



there has to be a way to express this.... or is there?

Share this post


Link to post
Share on other sites

http://www.autoitscript.com/forum/index.php?showtopic=95285&view=findpost&p=685320

Global $s_search_str = "G283H88620TS"
Global $a_examples[6] = [5, "MX0G2H883H7426288620TS", "I..SHOULD..BE..FALSE", "UIERG283H88620TSERU", "G283H88620TSKDJF", "KEKEG283H88620TS"]

For $i = 1 To $a_examples[0]
    ConsoleWrite($a_examples[$i] & @TAB & "Is Valid = " & (_String_InStrRandom($a_examples[$i], $s_search_str) <> 0) & @CRLF)
Next

Func _String_InStrRandom($s_search_str, $s_char_str, $v_case = 0)
    Local $s_case = "(?i)"
    If $v_case Then $s_case = ""
    Return StringRegExp($s_search_str, "^" & $s_case & StringRegExpReplace($s_char_str, "(.)", "\.*?$1\.*?") & "\z")
EndFunc

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites

hey smoke =)

that works =) but it takes super long i guess because its not sql thats doing the string compares

wish sql could!

someone gave me this but i get an error (thinks the table name is the serial number)

_SQLite_Query(-1, "SELECT * FROM aInventoryInfo WHERE SerialNumber LIKE ('%' + SUBSTRING(" & $serial & ",LEN(" & $serial & ") / 2, LEN(" & $serial & ") - (LEN(" & $serial & ")/2))) AND CatType='MONITOR'", $hQuery)

Share this post


Link to post
Share on other sites

hey smoke =)

that works =) but it takes super long i guess because its not sql thats doing the string compares

wish sql could!

someone gave me this but i get an error (thinks the table name is the serial number)

_SQLite_Query(-1, "SELECT * FROM aInventoryInfo WHERE SerialNumber LIKE ('%' + SUBSTRING(" & $serial & ",LEN(" & $serial & ") / 2, LEN(" & $serial & ") - (LEN(" & $serial & ")/2))) AND CatType='MONITOR'", $hQuery)

Interesting that you say it didn't work in the first one, because the first one worked for me as well.

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites

nooo ur script works.. just takes super long bc the db is 50k records.

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

nooo ur script works.. just takes super long bc the db is 50k records.

I was speaking of Martins code.

I'm not sure about SQL stuff though, so good luck with that.

Edit:

Maybe this could help:

http://www.sqlite.org/lang_expr.html

Edited by SmOke_N

Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites

thanks smoke =)

yea hopefully - im suprised this cant be done in sql alone though.

Share this post


Link to post
Share on other sites

what i ended up doing is breaking up each character in the string into seperate variables

then did a LIKE '%$char1%$char2%$char3....%"

a bit slow but works great!

thanks for your help =)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0