Sign in to follow this  
Followers 0
porya

SQL Search string in column

17 posts in this topic

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 :(

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

Edited by matwachich

Share this post


Link to post
Share on other sites

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  :(

Share this post


Link to post
Share on other sites

This code does not work

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

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

i got this mean " returns no values"

Share this post


Link to post
Share on other sites

?

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)


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

1 person likes this

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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 || '%'; "

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 by porya

Share this post


Link to post
Share on other sites

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

it shows first record of database

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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. :)

#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

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

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