handle SQL special characters

Question

I need to SELECT the following table of values in PostGIS 2.2:

nennweite character varying (10)

1",
100,
110,
125,
150,
200,
250, 
...

These values type is varchar but i wan't to select them as numeric values so i tried

SELECT DISTINCT
CASE
WHEN lei.nennweite = '1"' THEN 25.4
ELSE CAST(lei.nennweite AS numeric)
END as d2
FROM public."WAS_LEITUNG" lei, qwat_vl.pipe_material vl_pipe_mat
WHERE lei.material = 1 AND vl_pipe_mat.value_fr = 'Fonte' 
GROUP BY d2, vl_pipe_mat.id
ORDER BY d2

i get the following error

ERROR: Invalid input syntax for type numeric: « »

I tried many way to handle the ' " ' like using backslash, double quote, escape but i didn't manage to solve my problem.

Why GROUP BY when no aggregate functions are involved? And combined with SELECT DISTINCT? — jarlh, May 19 '17 at 13:49
I use group by because it's just a part of the initial query which was selecting on different row of different table i just forgot to remove it for the topic but i doesn't change anything here. — Arnaud Breitwieser, May 19 '17 at 14:05
The spaces are just empty, i don't have any Nulls in lei.nennweite — Arnaud Breitwieser, May 19 '17 at 14:06

score 1 · Answer 1 · edited May 23 '17 at 11:55

The cast to numeric will fail if the input is not, well, a number. You can either ensure your input is valid and handle the 2 cases where it is not, or you can use a function that returns NULL if the input cannot be cast to numeric.

The error message do mention the type of entry that created the issue, in this case a white space. You would handle it by replacing it by NULL (or any value you deem suitable)

CASE
  WHEN lei.nennweite = '1"' THEN 25.4
  WHEN lei.nennweite = ' ' THEN NULL
  ELSE CAST(lei.nennweite AS numeric)
END as d2

or using the isnumeric() function:

 CASE
      WHEN lei.nennweite = '1"' THEN 25.4
      WHEN isnumeric(lei.nennweite) THEN CAST(lei.nennweite AS numeric)
      ELSE NULL
 END as d2

On a side note, you may want to look for ", and if found remove it from the input, then read the number, and finally multiply it by 25.4. It would ensure an input like 2" or 1.5" will be processed.

score 0 · Answer 2 · answered May 19 '17 at 16:27

0

you may want to try to_number(string, formatStr) instead of cast.

SELECT to_number(x, '9999999')
FROM ( VALUES
  ( '1"' ),
  ( '100' ),
  ( '110' ),
  ( '125' ),
  ( '150' ),
  ( '200' ),
  ( '250' )
) AS t(x);

answered May 19 '17 at 16:27

Evan Carroll

78,363
46
261
468

handle SQL special characters

2 Answers2