Comparing dollar amounts stored in a varchar

[mark.a.lemoine]

I need to be selecting records based on the value of a string-format
currency value.

Specifically, I have a table with a field of type varchar(50). Stored
in this field is a dollar-formatted value, complete with the dollar
sign and commas for the thousands separator (and an optional leading
minus sign for negative values). For instance: $1,234.56.

One restriction I have to live with is that I can't change the schema
of the table or field - it's gotta stay as a varchar (unfortunately).

I've found that the decimal function doesn't like the dollar sign and
commas, so I've also found that using the replace function to strip
them out works. So a query that returns records that have a value
greater than $10 looks like this:

select * from myTable where
decimal(replace(replace(myField,'$',''),',',''),11 ,2) > 10

Although this works, it seems that this just can't be efficient, so my
question is whether there is a better way to accomplish this? While
sticking with the restriction of no schema change, naturally. :)

Thank you
Mark

[Hardy]

add a generated column
compField decimal(11,2) generated always as
(decimal(replace(replace(myField,'$',''),',',''),1 1,2))

or create a new table with that.

[Brian Tkatch]

If the field always has the same amount fo numbers, we can use
Tonkuma's trick:
VALUES TRANSLATE('BDEFGHI', '$1,234.56', 'ABCDEFGHI')

Either way, you should probably make this a DETERMINISTIC FUNCTION to
return a DECIMAL(6,2) or whatever your requirements are.

Also, if you can add a COLUMN, Hardy's idea of a GENERATED ALWAYS
COLUMN would be an excellent answer to this issue.

B.

[Tonkuma]

I couldn't also imagine another way than use REPLACE.
But, following expression may be a little better.
DECIMAL(REPLACE(SUBSTR(your_column,2),',',''),31,2 )
or, if data is not left justified.
DECIMAL(REPLACE(SUBSTR(LTRIM(your_column),2),',',' '),31,2)
Because, schema of SUBSTR and LTRIM are SYSIBM that means they are
Built-in functions, schema of REPLACE is SYSFUN that means it is a
User-defined function, according to the description in the first page
of "SQL Reference Volume 1 Chapter 3. Functions".
Another issue is that your column length is 50. So, it will be better
to specify maximum precision of decimal data type, if you don't know
maximum data in the column. Of cause, if you know precisions of all
actual data in the column is less than or equal to 11, you should
specify it.