Using trim() function in sqlite

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,
 

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:

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:

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"
 

mozaharul

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

Best regards,
 

Attachments

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.
 

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,
 

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,
 

Erel

Administrator
Staff member
Licensed User
You should add in sub App_Start:
B4X:
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:
B4X:
    vbdate=SubString("'"&table1.Cell("bdate",i)&"'",1,space_position[U][B]-1)[/B][/U]
BTW, why do you add the first and last apostrophes?
 

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,
 

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.
 
Top