3 Replies Latest reply on Sep 20, 2016 9:51 AM by kettan

    Eliminate multiple decimal point entries in a varchar field

    Dan Cahill

      I'm working in a database where many users enter data without validation (specifically, a teacher gradebook program in a district with 300+ secondary school teachers).

       

      The data is stored in a Microsoft SQL server. Scores for individual assignments are entered in a varchar field. Converting to float throws an error if there are any improperly formatted numbers (e.g. 3+, 5', 4\ etc).

       

      I've been able to clean those up using Contains() in a calculated field and turning those fields to null, which allows me to leave the original field as a string, and convert the calculated field as a float except for one sticky problem.

       

      A number of entries have multiple decimal points, such as 1.23.46, or 345.3894.12. I don't want to filter out all entries with decimal points - is there a way to write a calculated field that only identifies these kinds of entries?