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
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Cast number to varchar in select" Watch "Cast number to varchar in select" New topic
Author

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.

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
Greenhorn

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
Bartender

Joined: Oct 21, 2008
Posts: 1107

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

Joined: Oct 14, 2005
Posts: 18541
    
    8

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