Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

sum(case when regexp_instr(...)

sum(case when regexp_instr(...)

sum(case when regexp_instr(...)


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,

RE: sum(case when regexp_instr(...)

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

RE: sum(case when regexp_instr(...)

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.



RE: sum(case when regexp_instr(...)

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','))) / LENGTH('Text')


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

RE: sum(case when regexp_instr(...)

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

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(...)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close