Follow our news:

Follow canonburysvcs on Twitter Follow Canonbury Services on Facebook Follow Canonbury Services' news by RSS Follow Canonbury Services' news by Atom Follow Canonbury Services' news by email


Numeric data in non-numeric fields

28/02/2011 – in SQL queries

Sometimes in GoldMine it’s necessary to use a text field to hold numeric data. Examples are the in Key Fields and in Details.

The only difficulty comes when you want to sort the data. Because the numbers are being interpreted as characters (you can think of them as letters), ’20’ will come after ‘1000’ – just as ‘ball’ will come after ‘antidisestablishmentarianism’.

In a SQL query there’s a very simple fix though: just change the way that SQL interprets the characters. For example, to sort numeric data in the contsupref field of a Detail, try the following:

select * from contsupp where contact='Your Detail Name' order by cast(contsupref as int)

The ‘cast’ function turns the text values into integers.

Note that if the query hits a value that can’t be converted to a number then you will get an error.