Using trim() function in sqlite

Discussion in 'Questions (Windows Mobile)' started by mozaharul, Nov 30, 2008.

  1. mozaharul

    mozaharul Active Member Licensed User

    Hi,

    After converting a CSV file the column having date appears like :30/11/1980 0:00:00 (with timestamp)
    I tried to use the trim() function in the sqlite to get rid of those timestamp values ,like:

    Select trim(bdate,0) from birth;

    But says the error : “no such function : trim”

    Please help.

    regards,
     
  2. DaveW

    DaveW Active Member Licensed User

    Trim is part of Andrew Graham's StringsEx library (http://www.basic4ppc.com/forum/additional-libraries/2022-stringsex-library.html#post10957).

    Add stringsex.dll to the components then add an object StringsEx (named e.g. StingObj). You can then use StringObj.trim(bdate)

    However, that will just remove leading and trailing blanks. To get rid of the time portion of the string I think you will need to use SubString & StrIndexOf.
    SubString(bdate,0,10) will return the date portion of the string. If the date uses "1/1/1980" type dates rather than "01/01/1980" you will have to use StrIndexOf to get the correct length (find the position of the space after the year).
     
    Last edited: Nov 30, 2008
  3. Erel

    Erel Administrator Staff Member Licensed User

  4. DaveW

    DaveW Active Member Licensed User

    A much better solution of course!

    I bow to Erel the Master :sign0027:
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    Lol :)
     
  6. mozaharul

    mozaharul Active Member Licensed User

    Hi Erel and DaveW,

    I’m elaborating what I did to have help from you. I have a listbox and a table. The table is populated by the selection changed (name of individuals) event of the listbox.

    Sub listname_SelectionChanged (Index, Value)
    ... ...
    If listname.SelectedIndex >=0 Then
    cmd3.CommandText="select bdate,hpid from hhmem where name = "& "'"&listname.Item(listname.SelectedIndex)&"'" &" order by hpid"
    cmd3.ExecuteTable("table18",0)
    End If
    ... ...
    End Sub

    The table still shows the timestamp part in the “bdate” column of tableX.
    Then used the strIndexof and substring to get rid of timestamp part in the TableX_SelectionChanged event and it works :

    Sub TableX_SelectionChanged (ColName, Row)
    ... ...
    i=tableX.SelectedRow
    space_position=StrIndexOf("'"&tableX.Cell("bdate",i)&"'"," " ,0)
    ‘ vbdate is a global variable
    vbdate=SubString("'"&tableX.Cell("bdate",i)&"'",1,space_position)
    Msgbox(vbdate)
    ... ...

    End Sub

    I want the timestamp part would not be shown in the tableX.
    Then I tried as per Erel’s suggestion :


    Sub listname_SelectionChanged (Index, Value)
    ... ...
    If listname.SelectedIndex >=0 Then
    cmd3.CommandText="select strftime('%d-%m-%Y','bdate')as Bdate,hpid from hhmem where name = "& "'"&listname.Item(listname.SelectedIndex)&"'" &" order by hpid"
    cmd3.ExecuteTable("tableX",0)
    End If
    ... ...
    End Sub

    But the tableX shows “null” in the “bdate” column.

    Please correct me.


    Regards,
     
    Last edited: Dec 2, 2008
  7. Erel

    Erel Administrator Staff Member Licensed User

    Can you upload a small database file the code required to populate the list?

    BTW, there is no need to concatenate apostrophes with &.
    "This is a 'legal' string"
     
  8. mozaharul

    mozaharul Active Member Licensed User

    Hi Erel,
    I really appreciate for so quick reply. Yes, I attached a database for you.

    Best regards,
     

    Attached Files:

    • db.zip
      File size:
      336 bytes
      Views:
      82
  9. mozaharul

    mozaharul Active Member Licensed User

    Hi Erel,

    I attached a small program for you,


    Best regards,
     

    Attached Files:

  10. Erel

    Erel Administrator Staff Member Licensed User

    The problem is that the time string should be:
    30/11/1980 00:00:00
    instead of:
    30/11/1980 0:00:00

    The second string is not a valid time string.
     
  11. mozaharul

    mozaharul Active Member Licensed User

    Hi Erel,

    I attached again the program along with database for a new problem. In the program using Substring function I can pick the date part from the database, but having error feeding that date to the DateTimePicker or to the Calendar control.

    Please help.


    regards,
     
  12. Erel

    Erel Administrator Staff Member Licensed User

    What error do you get?
     
  13. mozaharul

    mozaharul Active Member Licensed User

    Hi Erel,

    Thanks for your time. If you run the attached program, the date part is extracted from the database and assigned to variable "vbdate".

    Msgbox(vbdate) ;shows the date.

    When this variable is used like:

    dtp.value=dateparse(vbdate) ;dtp is DateTimePicker.

    It says the date value not in the range of Mindate and Maxdate.

    If I use :Msgbox(dateparse(vbdate)) ; shows tick value is zero (0).

    I understand that since the tick value return is zero (0), so the date shown is not a date string. I tried to make the date shown a string but failed to have the desired result.

    Any suggestion for me ?

    regards,
     
  14. Erel

    Erel Administrator Staff Member Licensed User

    You should add in sub App_Start:
    Code:
    DateFormat("dd/m/yyyy")
    Note that you need only one 'm' to match your string format.

    Currently when you parse the date string you leave the space character.
    So you should change it to:
    Code:
    vbdate=SubString("'"&table1.Cell("bdate",i)&"'",1,space_position[U][B]-1)[/B][/U]
    BTW, why do you add the first and last apostrophes?
     
  15. mozaharul

    mozaharul Active Member Licensed User

    Hi Erel,
    Thanks and I appreciate your help.

    Regarding the apostrophes, since the syntax for “SubString” function: SubString (String, StartIndex, Count), to make the extracted date part a string I used the apostrophes.
    I tried other options but give error.

    Another query to you, as I extract record(s) from 165699 records of an old census, it takes bit time on the device whether it’s possible to use some “wait window” by the time device remains locked. I tried to use a separate Form, the Form is shown as the selection change happened in the listbox and Form disappears after the table is populated:
    … …
    cmd.ExecuteTable("table1",0)
    form2.Close

    The Form (“wait window”) shown in full size, reducing it’s size effects all other Forms.
    Using the “Msgbox()” for the same, there is “OK” button which needs user interaction.
    Is there any other option to do the same ?

    Best regards,
     
  16. Erel

    Erel Administrator Staff Member Licensed User

  17. mozaharul

    mozaharul Active Member Licensed User

    Thanks again, I'll try.


    regards,
     
  18. klaus

    klaus Expert Licensed User

    Instead of using a Form you could use a Panel, you can adapt it's size as you want, put text on it with Labels and display and hide it with the Visible parameter. Or you could use a ProgressBar if you know the number of records to retreive, but depending on how often you update the bar it would unfortunately slow down the process.

    Best regards.
     
  19. mozaharul

    mozaharul Active Member Licensed User

    Hi klaus,

    The waitcursor works fine. I'll try to use the panel option for the same.


    best regards,
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice