Jump to content

StringRegExp expressed in _SQLite query


gcue
 Share

Recommended Posts

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
Link to comment
Share on other sites

  • Moderators

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.

Link to comment
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)

Link to comment
Share on other sites

  • Moderators

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.

Link to comment
Share on other sites

  • Moderators

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.

Link to comment
Share on other sites

  • 2 weeks later...

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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...