Page 1 of 1
Convert number into symbol in query
Posted: Mon Mar 19, 2012 9:48 am
by poisonnnn
Sorry for bothering you guys. Wanna ask you smth. In LiveCode
If I have a result in MySQL like:
SELECT days from travel;
I get:
column1
1234567
123456-
123456-
123----
12345-7
(days from monday till sunday)
Question.
How can I make my result in form look like:
when i get for example: 1234567 -> i should get result +++++++, 123-56- I would get +++-++- .
Help please.
Re: Convert number into symbol in query
Posted: Mon Mar 19, 2012 10:16 am
by bn
Hi Marek,
I took your sample data and put it into a field, made a second field for the result. Assuming your data is consistent I did not do error checking. In a button this code:
Code: Select all
on mouseUp
put field 1 into tData
repeat for each line aLine in tData
repeat for each char aChar in aLine
if aChar is a number then
put "+" after tCollect
else
put aChar after tCollect
end if
end repeat
put cr after tCollect
end repeat
delete last char of tCollect -- cr after last line deleted
put tCollect into field 2
end mouseUp
make shure you understand what is going on in the script.
Lets see what your double posting to the use-list will come up with.
Kind regards
Bernd
Re: Convert number into symbol in query
Posted: Mon Mar 19, 2012 11:25 am
by Mark
Hi Marek,
In one line:
Code: Select all
put replaceText(mySqlData,"[1-7]","+")
Kind regards,
Mark
Re: Convert number into symbol in query
Posted: Tue Mar 20, 2012 10:13 am
by poisonnnn
Hi b, Mark.
Thank you guys for the replies!
Both of your scripts worked perfectly, tested. I have another question.
In my query there are more than one column with "WHERE..AND.." filters and so one... Sooo... I wanna make my days result in the separate columns in the result, but when I do this way + script of yours:
SELECT car_nr, model, travel_days FROM table1,table2 WHERE number <> 414 AND (car_nr > truck_nr) from transport;
So my result will be smth like this:
car_nr model travel_days
424 PAS1 123456-
425 PAS2 123--6-
426 PAS3 1234--7
The problem is that there are other results with numbers, so car_nr will aslo be also +++.
I need results with separate columns, should look lite this.
col1 col2 col3 col4=2 col5=3 and so on...
FIELD 1:
car_nr model 1 2 3 4 5 6 7
424 PAS1 + + + + + + -
425 PAS2 + + + - - + -
426 PAS3 + + + + - - +
or it could be in the seperate fields (field 1 & field 2), (even better would be, but the result should be the same.):
FIELD 1:
car_nr model
424 PAS1
425 PAS2
426 PAS3
FIELD 2:
1 2 3 4 5 6 7
+ + + + + + -
+ + + - - + -
+ + + + - - +
Maybe there is a way to hide columns it the result, like:
SELECT car_nr, model, travel_days FROM table1,table2 WHERE number <> 414 AND (car_nr > truck_nr) from transport;
and show only result of the colums travel_days, but ONLY on separate columns or jus hide other columns.
Help me guys....
Re: Convert number into symbol in query
Posted: Tue Mar 20, 2012 11:12 am
by bn
Hi Marek,
using Mark's wonderful one-liner
Data in field 1:
424 PAS1 123456-
425 PAS2 123--6-
426 PAS3 1234--7
without header
use this code
Code: Select all
on mouseUp
put field 1 into tData
repeat for each line aLine in tData
put word 1 to 2 of aLine & return after tPart1
put word 3 of aLine & return after tPart2
end repeat
delete last char of tPart1 -- return
delete last char of tPart2 -- return
put "car_nr model" & return & tPart1 into tPart1 -- make header
put replaceText(tPart2,"[1-7]","+") into tPart2 -- Mark's regular expression
put "1234567" & return & tPart2 into tPart2 -- make header
put tPart1 into field 2
put tPart2 into field 3
end mouseUp
to put the first part into field 2 and the second part into field 3
Kind regards
Bernd
Re: Convert number into symbol in query
Posted: Tue Mar 20, 2012 12:17 pm
by poisonnnn
Thank you very much!
Works clean
.
Marek.