Solved

how to determine the number of instances of a character in  string in Oracle

Posted on 2014-09-30
8
323 Views
Last Modified: 2014-09-30
I want to determine how many of a certain letter in a string, e.g., MTWRF10,TR34,TBD

these are class periods, so M=Mon, T=Tues, R=Thurs, etc.

I want to determine how many M's, for example. I can't figure out how to do this in Sql, plus PL/SQL is probably better, no ?

I guess it would just be walking character by character and counting up, I suppose. The logic processes a day at a time, so as I process Monday, I would just walk through the string and count my M's (while ignoring TBD, etc.).

So if there is a Sql trick, please advise. If not, and you have a code snippet to address, that would be excellent.

(oracle is such a broad term, but that looks like the only topic that applies, I don't see PL/SQL).

(I also don't see where to assign points anymore . . . is everything now 500 points ???)
0
Comment
Question by:Gadsden Consulting
  • 4
  • 4
8 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40352653
Single character?

length(string) - length(replace(string,'M'))
0
 

Author Comment

by:Gadsden Consulting
ID: 40352656
yes, single character.

very slick ! (I should have thought of that . . . )
0
 

Author Comment

by:Gadsden Consulting
ID: 40352670
just what the patient needed !

Thx.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40352693
Just thought of something.

You wanted to ignore TBD but what I posted will include the 'T' when looking for Tuesday.

Simple fix.  Just grab everything up to the first comma and do the length as above.

There are a few ways but try this:
regexp_substr('MTWRF10,TR34,TBD','[^,]+')

If you are dealing with a LOT of data this one might be faster:
substr(:'MTWRF10,TR34,TBD',1,instr(:'MTWRF10,TR34,TBD',',')-1)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40352696
>>(I also don't see where to assign points anymore . . . is everything now 500 points ???)

Looks like I even missed that change.  I was just informed that everything is now 500 points.
0
 

Author Comment

by:Gadsden Consulting
ID: 40353534
thanks - I used replace TBD with ''

regexp_substr looks good but I don't understand what is going on there . . .

same with the substr / instr . . . not sure there.

I will try both out tomorrow.

yeah on the 500 points - grade inflation !
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40353597
The magic is [^,]+

This looks for 1 or more characters '+'  that is NOT '^'  in this list '[]' of characters.  In this case only one, a comma ','


>>same with the substr / instr . . . not sure there.

instr returns the position of the first comma.  Then I subtract one position to remove the comma (really unnecessary for the regex), then grabs the substring from position 1 to that number.

>>grade inflation !

Changes at corporate.
0
 

Author Comment

by:Gadsden Consulting
ID: 40353602
ok, thanks for the expln . . . I'll try tomorrow.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now