Sunday, June 26, 2005

Tricky bitcount

SQL> -- it's a tricky one... for those who have UTF8
SQL> -- no optimization or whtsoever, just an idea...
SQL> SET VERIFY OFF
SQL> VAR n NUMBER
SQL> VAR i VARCHAR2(30);
SQL> EXEC :i := '0123456789ABCDEF';

PL/SQL procedure successfully completed.

SQL> COLUMN value FORMAT A12
SQL> SELECT value
2 FROM nls_database_parameters
3 WHERE parameter = 'NLS_NCHAR_CHARACTERSET'
4 /

VALUE
------------
UTF8

SQL> SELECT value
2 FROM nls_database_parameters
3 WHERE parameter = 'NLS_CHARACTERSET'
4 /

VALUE
------------
UTF8

SQL> DEFINE b2=C280
SQL> DEFINE b3=E0A080
SQL> DEFINE utf8=&b2&b2&b2&b3&b2&b2&b3&b2&b3&b3
SQL> BEGIN
2 :n := LENGTHB(
3 REPLACE(
4 TRANSLATE(:i
5 , '35679ABCDE0'
6 , utl_raw.cast_to_nvarchar2('&utf8')
7 )
8 , 'F'
9 , '1111'
10 )
11 );
12 END;
13 /

PL/SQL procedure successfully completed.

SQL> PRINT n

N
----------
32

SQL> EXEC :i := 'AC001DEADBEEF';

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> PRINT n

N
----------
29

No comments: