Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sum(case when regexp_instr(...)

Status
Not open for further replies.
JustATheory

JustATheory

IS-IT--Management
Feb 27, 2003
115
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dEX2340
  • Locked
  • Question
SQL: Select Case when ... 2
Replies
12
Views
624
r937
r937
dEX2340
  • Locked
  • Question
Using CONVERT in CASE WHEN
Replies
4
Views
304
dEX2340
dEX2340
faimuj
  • Locked
  • Question
Trying to get MIN MAX with CASE statement 1
Replies
4
Views
282
faimuj
faimuj
OracleProgrammerwith
  • Locked
  • Question
CASE STATEMENT WHERE CLAUSE ISSUE
Replies
1
Views
189
Thargy
Thargy
lukefuller01
  • Locked
  • Question
Joining multiple tables - sum
Replies
3
Views
231
IanWaterman
IanWaterman

Part and Inventory Search

Sponsor

Back
Top