Why can not be counted the pipe chars in string?

Question

select Values, REGEXP_COUNT(values,'|') from Products

Sample Values cell:

Product|Cash|Loan|Customer

Result of select is 27. If count any other char it works, but if I want to count the | it counts the whole string. Why, and how can I count the pipes?

You shouldn't be storing delimited values like that in a single column to begin with — a_horse_with_no_name, Jun 12 '18 at 08:25

score 5 · Answer 1 · answered Jun 12 '18 at 08:23

5

That's because | has a special meaning in regular expressions. If you want them to be read as normal characters you need to escape them, using \|.

answered Jun 12 '18 at 08:23

kabanus

22,925
6
32
68

Yes, the pipe symbol means OR see https://www.techonthenet.com/oracle/functions/regexp_count.php – Dijkgraaf Jun 12 '18 at 08:24

score 1 · Accepted Answer · answered Jun 12 '18 at 08:29

To count specific character you could also use:

SELECT col, LENGTH(col) - LENGTH(REPLACE(col, '|')) AS pipe_count
FROM tab;

DBFiddle Demo

Output:

┌────────────────────────────┬────────────┐
│            COL             │ PIPE_COUNT │
├────────────────────────────┼────────────┤
│ Product|Cash|Loan|Customer │          3 │
└────────────────────────────┴────────────┘

Why can not be counted the pipe chars in string?

2 Answers2