This week's book giveaway is in the Design forum.
We're giving away four copies of Building Microservices and have Sam Newman on-line!
See this thread for details.
The moose likes JDBC and Relational Databases 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 Building Microservices this week in the Design forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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: 19728

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’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link:
subject: Cast number to varchar in select
It's not a secret anymore!