Convert number into symbol in query

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

[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

[Mark]

Hi Marek,

In one line:

Code: Select all

put replaceText(mySqlData,"[1-7]","+")

Kind regards,

Mark

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

[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

[poisonnnn]

Thank you very much!
Works clean .

Marek.