469,366 Members | 2,225 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

Comparing dollar amounts stored in a varchar

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

Mar 17 '06 #1
3 2048
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.

Mar 17 '06 #2
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.

Mar 17 '06 #3
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.

Mar 17 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Jarrod Morrison | last post: by
3 posts views Thread by Bilbo | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.