sum(case when regexp_instr(...)

[JustATheory]

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

 

[r937]

try replacing your entire SUM expression with the following:

(LENGTH(FieldName) - Length(REPLACE(FieldName,'Text'))) / LENGTH('Text')



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[IanWaterman]

What are you trying to do?

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

 

[r937]

thanks for bringing the thread back to active status, ian, it made me look closely at what i had written, and there was an error in it

it should be

(LENGTH(FieldName) - Length(REPLACE(FieldName,'Text'[red],''[/red]))) / LENGTH('Text')



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[CRoberts]

The RegExp_Instr appear fine. This returns the position that a substring begins at. If not zero, add 1 otherwise add a 0. Try modifying the CASE statement:

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.

 

[r937]

the problem with REGEXP and REGEXP_INSTR is that they aren't ANSI SQL

(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