Cast number to varchar in select

[Theodore David Williams]

I would like to form a select statement to cast a number column to a varchar so I can append some additional info in the response.

Ie
[code]
Select 'test-' || CAST(id as varchar(10)) as testcolumn from some_table;
[code]

However I cannot get this to work in MySQL and oracle. I would like this statement to stay database agnostic. Any ideas?

Thanks.

[Rajit vreddi]

Use To_char if you use oracle DB.

Ex: SELECT To_char(CUSTOMER_ID) || 'Simple string' FROM TB_CUSTOMER

[Theodore David Williams]

Please database agnostic. Ie I want one and only one (if possible) query that will work on oracle, MySQL and Postgres.

According to the docs CAST is a standard and implemented on all DBs just can't get this one query to work on MySQL.

[Wendy Gibbons]

If you want db agnostic you need to do it in java

[Paul Clapham]

Here's a link to the MySQL documentation for the CAST function. Note that "varchar" isn't one of the types it supports as a result type. You would have to cast to char instead.