PureBasic Forums - English

Code: Select all

ΓΙΑΝΝΗΣ
Γιάννης
Γιαννης

Code: Select all

SELECT * FROM Table WHERE UPPER(item) LIKE 'UPPER(%text%')

Code: Select all

#PatCmp_MatchAll = '%'
#PatCmp_MatchOne = '_'

Procedure.i PatCmp(*str.Unicode, *pat.Unicode, Array lut.u(1), esc.l = 0)
 
  ; wildcard matching algorithm based on code from
  ; http://dodobyte.com/wildcard.html
 
  Protected *pat_, *str_
 
  ; check arguments
  If *pat = #Null Or *str = #Null
    ProcedureReturn #False
  EndIf
 
  ; while *str has characters
  While *str\u
    If *pat\u
     
      ; handle 'match all'
      If *pat\u = #PatCmp_MatchAll
        *pat + 2
        If *pat\u = 0
          ProcedureReturn #True
        EndIf
        *pat_ = *pat
        *str_ = *str
        Continue
      EndIf
     
      ; handle escape character
      If *pat\u = esc
        *pat + 2
        If *pat\u = 0
          ProcedureReturn #False
        EndIf
      EndIf
		;Debug Chr(lut(*pat\u))
		;Debug Chr(lut(*str\u))
		;Debug "====="
      ; lookup characters and compare
      If lut(*pat\u) = lut(*str\u) Or *pat\u = #PatCmp_MatchOne
        *pat + 2
        *str + 2
        Continue
      EndIf
     
    EndIf
   
    ; characters didn't match
    If *pat_ = #Null
      ProcedureReturn #False
    EndIf
    *str_ + 2
    *str = *str_
    *pat = *pat_
   
  Wend
 
  ; *str has no more characters
  ; check if pattern ends with 'match all'
  While *pat\u = #PatCmp_MatchAll
    *pat + 2
  Wend
  If *pat\u = 0
    ProcedureReturn #True
  Else
    ProcedureReturn #False
  EndIf
 
EndProcedure



;EnableExplicit

;- Constants

#SQLITE_UTF16 = 4
#SQLITE_UTF8          = 1 

;- Imports

ImportC ""
  sqlite3_create_function16.i(*db, zFunctionName.p-unicode, nArg, eTextRep, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_result_int(*sqlite3_context, int)
  sqlite3_value_text16.i(*sqlite3_value)
EndImport


;- Globals

Global Dim LUT_Like.u(65535)


;- Procedures

Procedure LUT_Init()
 
  Protected c1.s = "ΑΆαάΒΓΔΕΈεέΖΗΉηήΘΙΪιίϊΐΚΛΜΝΞΟΌοόΠΡΣσςΤΥΎΫυύϋΰΦΧΨΩΏωώ"
  Protected c2.s = "ααααβγδεεεεζηηηηθιιιιιικλμνξοοοοπρσσστυυυυυυυφχψωωωω"
 ;CheckDatabaseUpdate(0, "INSERT INTO persons (name, weight) VALUES ('γιαννησ 1', '10')")
 ;CheckDatabaseUpdate(0, "INSERT INTO persons (name, weight) VALUES ('Γιάννης 2', '5')")
 ;CheckDatabaseUpdate(0, "INSERT INTO persons (name, weight) VALUES ('3 Γιαννησ', '20')")
 ;CheckDatabaseUpdate(0, "INSERT INTO persons (name, weight) VALUES ('4 ΓΙΑΝΝΗΣ', '30')")
  Protected.Unicode *c1=@c1, *c2=@c2
  Protected.i c
 
  For c = 0 To 65535
    LUT_Like(c) = c
  Next
 
  For c = 'A' To 'Z'
    LUT_Like(c) = c + 32
  Next
   
  While *c1\u And *c2\u
    LUT_Like(*c1\u) = *c2\u
    *c1 + 2
    *c2 + 2
  Wend
 
EndProcedure





ProcedureC Like(*context, argc, *argv.Integer)
 
  Protected.Unicode *pat, *str, *esc
  Protected.l esc
 
  ; get pattern and string
  *pat = sqlite3_value_text16(*argv\i)
  *argv + SizeOf(Integer)
  *str = sqlite3_value_text16(*argv\i)
 
  ; three argument LIKE
  If argc = 3
    *argv + SizeOf(Integer)
    *esc = sqlite3_value_text16(*argv\i)
    If *esc = #Null
      ProcedureReturn
    EndIf
    esc = *esc\u
    *esc + 2
    If *esc\u
      ProcedureReturn
    EndIf
  EndIf   
 
  ; set result
  sqlite3_result_int(*context, PatCmp(*str, *pat, LUT_Like(), esc))
 
EndProcedure




;- Main code
CompilerIf #PB_Compiler_IsMainFile
	LUT_Init()
	Define DB.i, StartTime.i, EndTime.i, i.i
	
	UseSQLiteDatabase()
	
	;DB = OpenDatabase(#PB_Any, "c:\users\doctorized\desktop\libdata.file", "", "", #PB_Database_SQLite)
	DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)
	If DB
	
	 DatabaseUpdate(DB, "CREATE TABLE students (fname text)")
	 ; CompilerIf Not #PB_Compiler_Debugger
	 ;   For i = 0 To 10000
	 ; CompilerEndIf
	  DatabaseUpdate(DB, "INSERT INTO students VALUES ('Μαιουσύ')")
	  DatabaseUpdate(DB, "INSERT INTO students VALUES ('Μαΐουςΰ')")
	  DatabaseUpdate(DB, "INSERT INTO students VALUES ('μαίουσυ')")
	  DatabaseUpdate(DB, "INSERT INTO students VALUES ('ΜΑΪΟΥΣΥ')")
	 ; CompilerIf Not #PB_Compiler_Debugger
	 ;   Next i
	 ; CompilerEndIf
	 
	  sqlite3_create_function16(DatabaseID(DB), "like", -1, #SQLITE_UTF8, #Null, @Like(), #Null, #Null)
	 
	  StartTime = ElapsedMilliseconds()
	  If DatabaseQuery(DB, "SELECT fname FROM Students WHERE fname LIKE '%μαιουσ%'")
	    While NextDatabaseRow(DB)
	      ;Debug GetDatabaseString(DB, 0)
	    Wend
	    FinishDatabaseQuery(DB)
	  Else
	    ;Debug DatabaseError()
	  EndIf
	  EndTime = ElapsedMilliseconds()
	 
	 
	    MessageRequester("Needed", Str(EndTime - StartTime))
	
	 
	  CloseDatabase(DB)
	EndIf
CompilerEndIf

Code: Select all

Procedure.s Upper4SQL(ColName$, Text$)
  Protected Result$
  Protected i
  Protected Pos
  Protected Char$
  Protected CapitalLetters$   = "ΑΒΓΔΕΖΗΘΙΚΛΜΝΞΟΠΡΣΤΥΦΧΨΩΣΆΈΉΊΎΏABCDEFGHIJKLMNOPQRSTUVWXYZ"
  Protected SmallLetters$     = "αβγδεζηθικλμνξοπρστυφχψωςάέήίύώabcdefghijklmnopqrstuvwxyz"
  Protected NewList Letters.s()

  For i = 1 To Len(Text$)
    Char$ = Mid(Text$, i, 1)
    Pos   = FindString(SmallLetters$, Char$)

    AddElement(Letters())
    If (Pos > 0)
      Letters() = "'" + Char$ + "','" + Mid(CapitalLetters$, Pos, 1) + "'"
    Else
      Letters() = "'" + Char$ + "','" + Char$ + "'"
    EndIf
  Next
  ;~ Start to ConCat the string
  Result$ = ""
  ForEach Letters()
    Result$ + "Replace("
  Next

  Result$ + ColName$

  ForEach Letters()
    Result$ + ", " + Letters() + ")"
  Next

  ProcedureReturn Result$
EndProcedure

; Example
Define sField.s = "LastName"
Define sQuery.s = Upper4SQL(sField, "βαΚ")
Define sSQL.s   = "SELECT * FROM Clients WHERE " + sQuery + " LIKE '%ΒΑΚ%' ORDER BY LastName COLLATE NOCASE;"
Debug(sSQL)