Query Help

[UKlee]

Hi all i was wondering if you could help me my proplem is this, I have a table in my database with a field that contains both numbers and txt and I would like to total or SUM the numbers but am unsure how to remove the txt can I do this in the query.

I know I can or would normally use the SELECT sum(TotalDistance) as total from mytable where id = 'thisperson' but with leters in the same filed this does not work.

can I and if so how remover or get the query to ignore the letters and sum only the numbers.

Table Field example:

TotalDistance

223 Nm
195 Nm
320 Nm
152 Nm

Thanks for looking have a nice day.

Kind regards

Lee

[cahva]

Its not going to look pretty but works:

SELECT SUM(LEFT(TotalDistance,LOCATE(' ',TotalDistance))) as total FROM mytable;