SQL Search string in column

[porya]

Hi guys

I would Like To know How I Can Find The SQL Query String That Look Like To Database column,

Database

+--------------+---------------------+
| ProNo         | OPT
+--------------+---------------------+
| 1FN3003    |     12
| 6GK72        |      13
+--------------+---------------------+

Exampl

String Search = '1FN3003-4PP00-0AA0'

code-Autoit  =  $sqlString = "SELECT * FROM Table_1 WHERE '1FN3003-4PP00-0AA0' LIKE ProNo% "

This code does not work

Please Help Me

[matwachich]

"SELECT * FROM Table_1 WHERE ProNo LIKE " & _SQLite_Escape("%" & $sSearchString & "%")

Edited November 22, 2014 by matwachich

[porya]

Hello Polymath

Thank you for your Reply

this code for SQLite

"SELECT * FROM Table_1 WHERE ProNo LIKE " & _SQLite_Escape("%" & $sSearchString & "%")

I would like to SQL  Server 

[porya]

This code does not work

"SELECT * FROM Table_1 WHERE ProNo LIKE " & _SQLite_Escape("%" & $sSearchString & "%")

[water]

Could you please be a bit mor specific?

"Does not work" doesn't help us very much to help you solve your problem.

Does this mean: "Returns no values", "I get too many values", "I only get records containing the wrong values", "The script crashes and shows an error message" etc.

[porya]

i got this mean " returns no values"

[Gianni]

?

you are trying to extract records where the content of first field is contained within your search string?

usually with the "LIKE" sql statements you achieve the inverse purpose,
namely you search all records where your search string is contained within the searched field (and not the inverse)

[porya]

i know it, but i want this string 1FN3003-4PP00-0AA0 search from database, while in my database, field that be find has only some of 1FN3003-4PP00-0AA0 .here is as follows:

Database

+--------------+---------------------+
| ProNo         | OPT
+--------------+---------------------+
| 1FN3003    |     12
| 6GK72        |      13
+--------------+---------------------+

please help in this search

[jchd]

Chimp,

Why not? The LIKE comparison operator is agnostic about what the source of the strings are: literals, result of function or operation, column content or any expression.

porya,

Try this:

$sqlString = "SELECT * FROM Table_1 WHERE '1FN3003-4PP00-0AA0' LIKE ProNo || '%'; "

Here I assume that the SQL engine you use recognizes || as the string concatenation operator.

[porya]

Hello Polymath

many Thank you for your prompt Reply

this code did not give anything even error

$sqlString = "SELECT * FROM Table_1 WHERE '1FN3003-4PP00-0AA0' LIKE ProNo || '%'; "

[jchd]

Are you sure the body of your code actually works?

When I try the same with SQLite, this request works as expected:

C:\Users\jc>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table table_1 (ProNo char, OPT int);
sqlite> insert into table_1 values ('1FN3003', 12);
sqlite> insert into table_1 values ('6GK72', 13);
sqlite> select * from table_1 where '1FN3003-4PP00-0AA0' LIKE ProNo || '%';
1FN3003|12
sqlite> .quit

And this elementary SQL request should be portable on any SQL engine worth its name.

[jchd]

I mean: does this other request yield a result at all with your code?

$sqlString = "SELECT * FROM Table_1;"
; ... proceed to SQL request and get result/error code

[porya]

expandcollapse popup

Local $server, $db, $username, $password, $ConnObj, $RezObj
$server = "PORYA\BPASERVER10"
$db = "po"
$username = ""
$password = ""

$ConnObj = ObjCreate("ADODB.Connection")
If $ConnObj = 0 Then
    MsgBox(0, "", "Error create ADODB.Connection", 2)
    Exit
EndIf

$RezObj = ObjCreate("ADODB.Recordset")
If $RezObj = 0 Then
    MsgBox(0, "", "Error create ADODB.Connection", 2)
    Exit
EndIf

$ConnObj.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";")

If $ConnObj.State = 0 Then
    MsgBox(0, "", "Error create Connect SQL", 2)
    Exit
EndIf
$Button1 = GUICtrlCreateButton("Button1", 112, 184, 97, 41)
While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1

$RezObj.ActiveConnection = $ConnObj


$sqlString = "SELECT * FROM Table_1 WHERE '1FN3003-4PP00-0AA0' LIKE ProNo || '%'; "   ; this is code
$QueryRez = $RezObj.Open($sqlString)

    $code = $RezObj.Fields(0).Value
    $name = $RezObj.Fields(1).Value



    $RezObj.MoveNext

       $ConnObj.Close

            $Input1 = GUICtrlCreateInput($code, 152, 48, 145, 21)
            $Input2 = GUICtrlCreateInput($name, 360, 48, 153, 21)
            

        
    EndSwitch
WEnd

Edited November 22, 2014 by porya

[jchd]

And what does "select * from Table_1;" give?

[porya]

it shows first record of database

[jchd]

Well, so SQL server is broken, which doesn't surprise me that much.

BTW you shouldn't create controls on the fly without ever destroying them, but since you destroy the connection object right after, you run no risk of accessing the DB after that.

[Gianni]

Chimp,

Why not? The LIKE comparison operator is agnostic about what the source of the strings are: literals, result of function or operation, column content or any expression.

porya,

Try this:

$sqlString = "SELECT * FROM Table_1 WHERE '1FN3003-4PP00-0AA0' LIKE ProNo || '%'; "

Here I assume that the SQL engine you use recognizes || as the string concatenation operator.

 

thanks jchd

learned something new.

I've used following snippet of code to test your query on a temporary SQLite DB built on the fly from an array, and it gives expected result.

Always nice to learn from you.

expandcollapse popup

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
Global $aResult, $iRows, $iColumns, $iRval

; ---------------------------------------------------------------------------------------------------------------------------
; populate array with data to be managed
Local $Array[3][2] = [["1FN3003", "12"],["6GK72", "13"],["1FN3003-4PP00-0AA0","22"]]
; ---------------------------------------------------------------------------------------------------------------------------
_SQLite_Startup()
_SQLite_Open()

_ArrayToSqlTable($Array) ; clone array to a temporary sql db
;                          name of "table" will be array
;                          name of "fileds" will be field0 field1 field2 fieldn ....

; --- use sql to manage the tabele -----------------------------------------------------------------------
$sqlString = "SELECT * FROM array WHERE '1FN3003-4PP00-0AA0' LIKE field0 || '%'; "
;
$iRval = _SQLite_GetTable2d(-1, $sqlString, $aResult, $iRows, $iColumns)
; --------------------------------------------------------------------------------------------------------
;
; --- show result of query ---------------------------------
If $iRval = $SQLITE_OK Then
    ; _SQLite_Display2DResult($aResult)
    _ArrayDisplay($aResult, "After SQL query")
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf
; ----------------------------------------------------------
_SQLite_Close()
_SQLite_Shutdown()
; -- the end --

Func _ArrayToSqlTable($Array) ; Pass an array to a temporary SQL table
    If Not IsArray($Array) Then Return SetError(1, 0, 0)
    Local $Dimensions = UBound($Array, 0)
    If $Dimensions > 2 Then Return SetError(2, 0, 0)
    Local $DBfields = ""
    Local $DBvalues = ""
    Local $records = UBound($Array, 1) - 1
    Local $fields = 0
    If $Dimensions = 2 Then
        $fields = UBound($Array, 2) - 1
    EndIf
    For $x = 0 To $fields
        $DBfields &= "field" & String($x) & ","
    Next
    $DBfields = StringTrimRight($DBfields, 1) ; remove the last comma

    If Not _SQLite_Exec(-1, "CREATE TEMP TABLE array (" & $DBfields & ");") = $SQLITE_OK Then
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg())
    Else
        For $x = 0 To $records
            For $y = 0 To $fields
                Switch $fields
                    Case 0 ; just 1 field (1D Array)
                        If IsNumber($Array[$x]) Then
                            $DBvalues &= $Array[$x] & ","
                        Else
                            $DBvalues &= _SQLite_FastEscape($Array[$x]) & ","
                        EndIf
                    Case Else ; multi fields (2D Array)
                        If IsNumber($Array[$x][$y]) Then
                            $DBvalues &= $Array[$x][$y] & ","
                        Else
                            $DBvalues &= _SQLite_FastEscape($Array[$x][$y]) & ","
                        EndIf
                EndSwitch
            Next
            $DBvalues = StringTrimRight($DBvalues, 1)
            ; insert row to table
            If Not _SQLite_Exec(-1, "INSERT INTO array VALUES (" & $DBvalues & ");") = $SQLITE_OK Then MsgBox(16, "SQLite insert Error", _SQLite_ErrMsg())
            $DBvalues = ""
        Next
    EndIf
EndFunc   ;==>_ArrayToSqlTable