REGEXP '^[0-9]+\.?[0-9]*$' THEN (0.0 round numbers

[Eric Kok]

I have a few columns in my db that are defined as text columns, but contain numbers. When I do a regular SQL query, I do get the numbers the proper way: 7,5

When I get the data in the datasource, it shows as 7, due to the fact that Syncfusion puts this in the SQL statement:

(CASE WHEN (`reporting_checklist_17_nl_NL`.`DefaultQuestion945`) REGEXP '^[0-9]+\.?[0-9]*$' THEN (0.0 + `reporting_checklist_17_nl_NL`.`DefaultQuestion945`) ELSE NULL END) AS `reporting_checklist_17_nl_NL_DefaultQuestion945`

When I change the data into 7.5 it works properly. But I don't want to change the data all over the place. 

How can I go around this without changing the query expression in Query View? 

Eric

[Anandaraj Radhakrishnan]

Hi Eric,   

  

Thank you for contacting Syncfusion Support.   

  

We hope you are using the MYSQL data source and tried to change the data column from Text to Number. If you want to change the data from ‘7,5’ to ‘7.5’, then currently, it is not possible in design view but it can be achieved through query view by doing some modification in query. You can use the below expression for particular column in query view to achieve your requirement.   

(CASE WHEN (REPLACE(`reporting_checklist_17_nl_NL`.`DefaultQuestion945`,',',".")) REGEXP '^[0-9]+\.?[0-9]*$' THEN (0.0 + REPLACE(`reporting_checklist_17_nl_NL`.`DefaultQuestion945`,',',".")) ELSE NULL END) AS `reporting_checklist_17_nl_NL_DefaultQuestion945`   

  

Regards,   

Anandaraj R