sum(case when regexp_instr(...)
sum(case when regexp_instr(...)
(OP)
Greetings,
I'm having trouble with this expression:
sum(case when regexp_instr(FieldName, 'Text') > 0 then 1 else 0 end) as NewFieldName
I want to read FieldName and sum the count of times 'Text' shows up and place that value in NewFieldName. 'Text' can show up in the string 0-n times, in real data 'Text' is varchar. What is happening is it finds the correct record but does not report the correct value in the new field. There is no pattern for the discrepancy, what am I doing wrong?
Any Help is welcomed,
JustATheory
I'm having trouble with this expression:
sum(case when regexp_instr(FieldName, 'Text') > 0 then 1 else 0 end) as NewFieldName
I want to read FieldName and sum the count of times 'Text' shows up and place that value in NewFieldName. 'Text' can show up in the string 0-n times, in real data 'Text' is varchar. What is happening is it finds the correct record but does not report the correct value in the new field. There is no pattern for the discrepancy, what am I doing wrong?
Any Help is welcomed,
JustATheory
Talk To Other Members
RE: sum(case when regexp_instr(...)
(LENGTH(FieldName) - Length(REPLACE(FieldName,'Text'))) / LENGTH('Text')
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: sum(case when regexp_instr(...)
Your Case will only ever return 1 no matter how many times the string "Text" appears in the Field.
If you want a count of the number of times Text appears then you will need to do something else.
Ian
RE: sum(case when regexp_instr(...)
it should be
(LENGTH(FieldName) - Length(REPLACE(FieldName,'Text','))) / LENGTH('Text')
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: sum(case when regexp_instr(...)
SELECT Username,
SUM(CASE RegExp_Instr(Username, 'SYS') WHEN 0 THEN 0 ELSE 1 END) AS Tmp_Count
FROM All_Users
WHERE Username <> 'SYS'
GROUP BY Username
ORDER BY 1;
I tested this in Oracle, but as RegExp and CASE works the same, should work anywhere. In Oracle 11g there is a RegExp_Count that would work also without the Group by and CASE.
RE: sum(case when regexp_instr(...)
(actually, quite a lot of Oracle syntax is not ANSI SQL)
LENGTH and REPLACE have a much better chance of being supported across various database products
hey JustATheory, would you kindly test one of the solutions you've been given in this thread and report back?
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon