Andy Hibbs

SQL Extract Number from a String

Discussion created by Andy Hibbs on Dec 24, 2016
Latest reply on Dec 28, 2016 by Andy Hibbs

We've an 84 column (don't you just love insurance company data extracts?"  SQL SELECT that we need to add a new entry  that needs a numeric value extracted from a string of text, such as '1000 per annum' we need '1000'.


I can do this in FileMaker either by simply GetAsNumber or using Position. However, this requirement has to be embedded within the SQL and I can't see a way around not having (according to FMP15 SQL Reference) CHARINDEX, PATINDEX or INSTR to use to find the first space within the string to use LEFT or NUMVAL or similar.


The only way I can see at the moment, as this particular example conveniently commences with the number, is to use a predefined number of entries in CASE searching for the first space using CHR. However, this is finite and as our currency includes Dirham, the figures can get pretty big and it wouldn't be robust coding.


I'm considering either cheating in FileMaker and taking the number extract into another field or even generating the report and then subsequently populating this particular column using standard FileMaker functions. But there are subtables involved and there is some degree of risk. These would be a last resort though.


Any constructive help much appriciated.


Kind regards



(p.s. Merry Christmas to all FM Developers and here's to a happy, healthy and prosperous New Year)