Sybase IQ: Converting a string to NUMERIC without error/exception

Question

I'm using Sybase IQ and need to convert a field from string to NUMERIC. The field sometimes has characters other than digits. In those cases I want it to return 0 instead of raising exceptions. How to do so?

You may look at the statements below to understand more:

SELECT CONVERT(NUMERIC(10, 0), '');
SELECT CONVERT(NUMERIC(10, 0), '1');
SELECT CONVERT(NUMERIC(10, 0), 'a');
SELECT CONVERT(NUMERIC(10, 0), 'a1');
SELECT CONVERT(NUMERIC(10, 0), '1a');

Only the first 2 lines will work. The remaining 3 lines will raise exceptions:

Cannot covert a to a NUMERIC(10, 0)(07006,-157)

Thanks in advance!

Answer 1

I am not sure if there is a better way but you could try this.

SELECT CASE PATINDEX('%[a-zA-Z]%','') 
   WHEN 0 THEN CONVERT(NUMERIC(10,0), '') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','1') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), '1') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','a') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), 'a') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','1a') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), '1a') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','a1') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), 'a1') 
   ELSE 0 END

Hope it helps :)