How to Display or Convert Number Values as Numbers In Text Field

I have a table with a column that has a text (varchar) data type. That column predominately has numeric values, but occasionally has text values. When there is a text value I need to show the value as-is. However, when there is a numeric value I need to convert it to a decimal value.

Here is an example

Because the first row contains the text “hrs”, it should result as-is and display 12 hrs. The second row, however, is truly a number and should display as 12.00.

When I use a “CASE” statement as shown below, I get the “CASE types character cannot be matched” error.

SELECT 
  [id], 
  [myvalue], 
  (CASE WHEN REGEXP_INSTR([value], '^[-+]?[0-9]*.?[0-9]+$')=0 THEN [value] ELSE CAST([value] AS DECIMAL(12,2)) END) AS [newvalue],
FROM 
  [mytable]
;

I know this is occurring because the data types for outputs for the “THEN” and “ELSE” need to match.

Any other ideas on how I could do this?

Couple notes:

1. I know that the “newvalue” column will still be considered a text data type.
2. I’m not simply trying to round or add “.00” to my result. The above is a simple example. The numeric values are varying and this really needs to be converted to decimal.

Answer

Because you are creating a column in a result set, it’s necessary that the column being created have a single type. In your case you are casting certain values to decimal which means your column will have to hold two types (varchar and decimal) and that isn’t going to fly.

Since at least one of your values is a varchar, they must all be (as a varchar can’t always be cast to other types like decimal or date or int or what-have-you).

Consider:

user=# SELECT CAST('1' AS DECIMAL(12,2));
 numeric 
---------
    1.00
(1 row)

user=# SELECT CAST(CAST('1' AS DECIMAL(12,2)) AS VARCHAR(100));
 varchar 
---------
 1.00
(1 row)

So here we have cast the string literal 1 to a decimal. The database adds the two decimal places in the representation of that decimal as expected. We then cast that thing back into a varchar() generating a string literal with two decimal places intact.

Using this in your SQL (modified a touch since I’m on postgres, not redshift):

user=# WITH mytable AS (SELECT 'car' as value UNION SELECT '1') 
       SELECT (CASE WHEN REGEXP_REPLACE(value, '^[-+]?[0-9]*.?[0-9]+$', '')=value 
           THEN value 
           ELSE CAST(CAST(value AS DECIMAL(12,2)) AS VARCHAR(100)) 
           END) AS newvalue 
       FROM mytable;
 newvalue 
----------
 1.00
 car
(2 rows)