Tuesday, July 8, 2008

Count The K's

I have this really annoying co-worker who happens to be the DBA. Everytime he walks by my desk he pounces on my keyboard. I've learned to Windows Key + L to lock the computer when I hear him approaching, but occasionally I forget.

As I'm standing near my unlocked computer he starts typing furiously in my sql*plus session (shouldn't he know better?). I should know better...

Walking away he asks, "How many K's are in there?"

I ignored him, but then wondered myself...what's the best solution to this problem?

So, here it goes:
DECLARE
l_count_k NUMBER := 0;
l_string VARCHAR2(300);
l_string_length INTEGER;
BEGIN
l_string := 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
TKELTKER';

l_string_length := LENGTH( l_string );

FOR i IN 1..l_string_length LOOP
IF SUBSTR( l_string, i, 1 ) IN ( 'K', 'k' ) THEN
l_count_k := l_count_k + 1;
END IF;
END LOOP;

dbms_output.put_line( 'Kk Count: ' || l_count_k );
END;
/
Easy enough, 45.

Then I started thinkinhg...can I do this in pure SQL? Of course!

SELECT 
SUM( CASE
WHEN SUBSTR( UPPER( mystring ), rownum, 1 ) = 'K' THEN
1
END ) k
FROM
dual,
(
SELECT 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
TKELTKER' mystring
FROM dual
) c
CONNECT BY LEVEL <= LENGTH( mystring )

COUNT_OF_K
----------
45

1 row selected.
I'm sure many of you can do better than that. So let's see 'em. Prodlife, this isn't a complicators test either. ;)

9 comments:

Anonymous said...

If you're in 11g, it's trivial:

SELECT regexp_count('W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEkT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
TKELTKER','K',1,'i') mystring
FROM dual

The "i" option makes this case insensitive.

oraclenerd said...

Cool...

I've meaning to learn the regex stuff, just haven't had the opportunity.

Thanks Tyler.

SydOracle said...

10g/11g makes it really easy
select regexp_count('W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;'||
'LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;LTKELTKER','[Kk]') c
from dual

Before regular expressions, this was the easiest method:

select length(v) - length(translate(v,' Kk',' '))
from
(select 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;'||
'LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;LTKELTKER' v
from dual)

Noons said...

why complicate?

SYS@wfdwdev=>SELECT
2 length('W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;
ERKT;LKERLTKERKT;EKT;LEKRT;LEK;LTKERTKERKT;
LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;
LEkT;LEKR;LTKETKERTKL;ERKTL;ERKTL;
ET;LEKT;LEKRT;LERTL;EKRL;TKELTERTKL;
ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;
ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;
EKL;KER;LTKE;LTKELTKER')
3 - length(
4 replace('W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;
ERKT;LKERLTKERKT;EKT;LEKRT;LEK;LTKERTKERKT;
LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;
LEkT;LEKR;LTKETKERTKL;ERKTL;ERKTL;
ET;LEKT;LEKRT;LERTL;EKRL;TKELTERTKL;
ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;
ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;
EKL;KER;LTKE;LTKELTKER','K')
5 )+1
6 FROM dual
7 /

LENGTH('W346OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK
--------------------------------------------------------------------------------
45

1 row selected.



:)

Anonymous said...

Here's another potential solution (although it's early, I might have missed an edge case ;)

select (length('XKKT') - length(replace('XKKT', 'K', ''))) as str_len from dual

I do prefer the regexp_count method though if you can use it, but it never hurts to have more options.

Anonymous said...

How about this? (Might avoid a regular expression engine invocation but still has some SQL string length restrictions).

select (
( length( c.mystring ) - length( replace( c.mystring, s.search ) )
/
length( s.search )
)
) count_of_replacements
from (
select 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK; LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL; EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L TKELTKER' mystring
from dual
) c
cross join
(
select 'K' search
from dual
) s;

Basically, find the length of the original minus the length of the original with the search string replaced and then divide the result by the length of the search string.

Could potentially micro-optimize by using WITH clauses or SYS_CONTEXT rather than cross joining two invocations of FAST DUAL.

Anyway, food for thought.

rhodry

Boneist said...

My attempt:

with my_tab as (select upper('W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;LTKELTKER') str
from dual)
select length(str) - length(replace(str, 'K'))
from my_tab;

Unknown said...

i prefer the old fashioned way ...

SQL> get r
1 with x as (select 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;LTKELTKER' y from dual)
2* select length(y),length(y)-length(replace(lower(y),'k')) from x
SQL> @r

LENGTH(Y) LENGTH(Y)-LENGTH(REPLACE(LOWER(Y),'K'))
---------- ---------------------------------------
282 45

Elapsed: 00:00:00.00

oraclenerd said...

Can you say "There's a million ways to skin a cat?"

Thanks everyone!