0

Because data is imported as string, I need to query string values as if they were integers, is there something I could use to cast values to string?

Where could I find documentation for this SQL in CartoDB? I found this

1

There are two ways. Screencap below

  1. Just recast it as a number using the user interface, you can find the option right under the column name.

  2. Select it dynamically with SQL, but there are only few cases where this makes sense.

cast

  • I am using SQL API and ::int is not working for me, this works: select * from my_table where field = '1' but when I try select *,field::int as xx from my_table where xx=1 is does not work, let me paste some trace – juanpastas Apr 12 '14 at 22:32
  • javascript console says: Failed to load resource: the server responded with a status of 404 (Not Found) the URL seems to be ok in web console when I inspect request parameters. – juanpastas Apr 12 '14 at 22:35
  • the conversion to int is probably failing on empty strings or strings. you might be better off doing the conversion in the interface first – andrewxhill Apr 14 '14 at 19:36
  • I tried on GUI too, same problem – juanpastas Apr 14 '14 at 22:47
  • Hmm... I'm not sure why our internal conversion isn't doing this for you, but I'll forward this along to get it fixed. In the meantime, try creating a new column with type numeric. Then do an, UPDATE tablename SET newcolumn = replace(regexp_replace(oldcolumn, '[^0-9.]','','g'),'', null)::integer – andrewxhill Apr 15 '14 at 12:56
0

Try select *, field::int xx from my_table where field::int=1

You can't use column alias in a WHERE clause. see this answer on stackoverflow

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.