Friday 25 June 2010

SIGNs of Friday

I just thought I'd share this thought that came to mind when looking for a solution to a simple problem.

I learnt DECODE before I encountered CASE, so often my brain thinks that way first.

Therefore, I came up with an expression that uses SIGN, which I'm sure you don't see often.

SELECT SUBSTR(product_description,1,40)
    ||DECODE(SIGN(LENGTH(product_description)-40),1,'...') product_description
   -- CASE WHEN LENGTH(product_description) > 40 THEN '...' END product_description
FROM oe.product_information;

The case equivalent is commented.

This place ellipses at the end of truncated strings longer than 40 characters.

1 comment:

Anonymous said...

Old habits are SO hard to break, eh? The second language I learned was Pascal, so I tend to think in CASE statements. A former co-worker liked to write DECODEs with SIGN embedded, which gave me a headache!

Happy Friday!