Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Cast number to varchar in select

 
Theodore David Williams
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use To_char if you use oracle DB.

Ex: SELECT To_char(CUSTOMER_ID) || 'Simple string' FROM TB_CUSTOMER
 
Theodore David Williams
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1110
Eclipse IDE Oracle VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want db agnostic you need to do it in java
 
Paul Clapham
Sheriff
Pie
Posts: 20948
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic