wood burning stoves*
The moose likes JDBC and the fly likes Cast number to varchar in select Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Cast number to varchar in select" Watch "Cast number to varchar in select" New topic

Cast number to varchar in select

Theodore David Williams
Ranch Hand

Joined: Dec 21, 2009
Posts: 102
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.

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

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

Rajit vreddi

Joined: Jul 05, 2012
Posts: 17
Use To_char if you use oracle DB.

Ex: SELECT To_char(CUSTOMER_ID) || 'Simple string' FROM TB_CUSTOMER
Theodore David Williams
Ranch Hand

Joined: Dec 21, 2009
Posts: 102
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

Joined: Oct 21, 2008
Posts: 1107

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

Joined: Oct 14, 2005
Posts: 18541

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.
I agree. Here's the link: http://aspose.com/file-tools
subject: Cast number to varchar in select
Similar Threads
How to Get values from defined Data Type
Problem in retieving user defined datatypes
Running a procedure in JDBC
How to Convert type in sql or jasper i report