## 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;LTKELTKER';  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 ) kFROM  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  ) cCONNECT BY LEVEL <= LENGTH( mystring )COUNT_OF_K----------        451 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. ;)

tylermuth 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.

Gary Myers 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.

:)

John Scott 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.

rhodry 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;

Raj J 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!