0

I need to run some math based on end user input, but for their sake we have added units of measurement to the available options for input into the table being queried. So I have a column that is populated with 6" or 1 ft and I only need the actual number to proceed with my equation. Is there a way to filter out the non-integer portions of my selection?

ended up using this

REVERSE(SUBSTRING(REVERSE(COLUMNNAME),2,4))

So it took 16" changed it to "61 selected the 61 then reversed back. my column won't ever be above 100 and the UOM will be the same, so if it were to be for ft, i would just modify it for that.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
danzlalune
  • 9
  • 4
  • Is it too late to redesign / modify the database and set the column as an integer? – JonTout Nov 30 '18 at 14:27
  • haha, no not necessarily, but for the ease of use, i don't want to modify it. i ended up reversing the query. `REVERSE(SUBSTRING(REVERSE(COLUMN, 2, 4)))` I needed a substring to be from right to left rather than left to right so this will do. – danzlalune Nov 30 '18 at 16:33

1 Answers1

0

See this overview about converting VARCHAR to INTEGER.

Short answer: it is not possible out of the box. But under "How to parse/retrieve only numbers from column values contains alpha-numeric characters?" they link to a custom function which can do what you need.

Stefan Winkler
  • 3,871
  • 1
  • 18
  • 35