none
Help sqlite command RRS feed

  • Question

  • Hi i have this sql comand that returns null

     

    SELECT     id, Name, Lastname, FuncNumb, [EplCode], Comments
    FROM         tbl
    WHERE     (Lastname = 'Carmo') AND (FuncNumb = '01') AND ([EplCode] = 'LENGTH (4)')

     

    Im tring to get the [EplCode] where the fields have the length of 4 leters, ex: 1365 and discard all oders; 3, 65, 698, 45789, etc..

     

    Ps: Using SQLite ADO.NET 2.0 Provider

     

    Regards

    nstlx

    Saturday, August 18, 2007 8:09 AM

Answers

  • When i look at http://www.sqlite.org/lang_expr.html:

    length(X) Return the string length of X in characters. If SQLite is configured to support UTF-8, then the number of UTF-8 characters is returned, not the number of bytes.


    Thus:

    SELECT     id, Name, Lastname, FuncNumb, [EplCode], Comments
    FROM         tbl
    WHERE     (Lastname = 'Carmo') AND (FuncNumb = '01') AND (LENGTH([EplCode]) = 4)


    Btw, i find it odd that FuncNumb  is compared with a string '01' as the Number in it's name suggests (to me atleast) that it's numeric...


    Wednesday, August 22, 2007 9:38 AM

All replies

  • Hi nstlx,

     

    You should use Len([EplCode]) = 4 instead of [EplCode]='LENGTH(4).

     

    SELECT     id, Name, Lastname, FuncNumb, [EplCode], Comments
    FROM        tbl
    WHERE     (Lastname = 'Carmo') AND (FuncNumb = '01') AND (Len([EplCode]) = 4)

     

    Regards

    Martin

    Tuesday, August 21, 2007 8:55 AM
  • Hi Martin,

    thanks for the reply, but i get this error:

     

    SQLite error
    no such function: Len

     

    Regards

    nstlx

    Tuesday, August 21, 2007 2:37 PM
  • Hi Nstlx,

     

    I move it from VB Express forum to .NET Framework Data Access and Storage forum for better responses, where ADO.NET experts live in.

     

    By the way, know about ADO.NET 2.0 Provider for SQLite.

    System.Data.SQLite is an open-source, enhanced version of the SQLite database engine for Windows.

     

     

    Thanks,

    Martin

    Wednesday, August 22, 2007 5:54 AM
  • When i look at http://www.sqlite.org/lang_expr.html:

    length(X) Return the string length of X in characters. If SQLite is configured to support UTF-8, then the number of UTF-8 characters is returned, not the number of bytes.


    Thus:

    SELECT     id, Name, Lastname, FuncNumb, [EplCode], Comments
    FROM         tbl
    WHERE     (Lastname = 'Carmo') AND (FuncNumb = '01') AND (LENGTH([EplCode]) = 4)


    Btw, i find it odd that FuncNumb  is compared with a string '01' as the Number in it's name suggests (to me atleast) that it's numeric...


    Wednesday, August 22, 2007 9:38 AM
  • Hi,
    First of all, big thanks for Martin Xie, for moving my post to the right place…. (Thanks).


    Hi,
    Timvw, thanks allot for the help, it works fine. (My problem was in Visual Basic 2005 Express query builder it was and is giving me an error whit this code) but ignoring the error it works fine thanks.

     

    PS: Timvw, all the columns fields are a text string, it is what you make of it that makes the difference, 01, is a seller, 02 driver etc…

    Best regards and thank you all for the great help
    nstlx

    Wednesday, August 22, 2007 10:44 AM