Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...
Database Forum / DB2 Topics / December 2007

Tip: Looking for answers? Try searching our database.

checksum validation

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
E.Bartosiewicz@gmail.com - 17 Dec 2007 08:30 GMT
I need to write some code that will validate data. I have a checksum,
that I need to calculate in a rather complicated way, so a simple
CHECK is not enough. Which is the best way to do that in DB2?

Ewa.
Reply to this Message
Lennart - 17 Dec 2007 09:08 GMT
On Dec 17, 9:30 am, E.Bartosiew...@gmail.com wrote:
> I need to write some code that will validate data. I have a checksum,
> that I need to calculate in a rather complicated way, so a simple
> CHECK is not enough. Which is the best way to do that in DB2?

If the validation involves other tables, IMO a before trigger is your
best option:

CREATE TRIGGER ...
NO CASCADE BEFORE INSERT ON ...
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
WHEN ( <condition> ) SIGNAL SQLSTATE ....

and a similar one for update of involved columns.

If rather complicated means complicated but concern only this table, I
would still aim for check constraint

My 2 skr

/Lennart
Reply to this Message
E.Bartosiewicz@gmail.com - 17 Dec 2007 09:44 GMT
> If rather complicated means complicated but concern only this table, I
> would still aim for check constraint

Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give me
some hints (or link to man) on how to make a check constraint, that
consists of loops and some operations on the data? I never wrote
checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
1986)

Ewa
Reply to this Message
Serge Rielau - 17 Dec 2007 11:18 GMT
>> If rather complicated means complicated but concern only this table, I
>> would still aim for check constraint
[quoted text clipped - 5 lines]
> checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
> 1986)
You have to write a function.
If it's as complex as you say an C UDF (eventually unfenced) woudl be
the way to go.
If it's just a couple of IFs and a loop, just do a SQL UDF.
My thumb rule is that SQL UDFs shouldn't be longer than a page.
(25 lines)

Cheers
Serge
Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message
E.Bartosiewicz@gmail.com - 17 Dec 2007 12:23 GMT
> E.Bartosiew...@gmail.com wrote:
> >> If rather complicated means complicated but concern only this table, I
[quoted text clipped - 13 lines]
> My thumb rule is that SQL UDFs shouldn't be longer than a page.
> (25 lines)

Thanks for your help!

So as I thought, I need a function. I still don't know how to write
one though... It's easy when you need a sequence of sql commends, but
I can't imagine how to do operations on text data and loops. Can you
recommend some tutorial?

Ewa
Reply to this Message
Knut Stolze - 17 Dec 2007 12:29 GMT
>> E.Bartosiew...@gmail.com wrote:
>> >> If rather complicated means complicated but concern only this table, I
[quoted text clipped - 20 lines]
> I can't imagine how to do operations on text data and loops. Can you
> recommend some tutorial?

Have a look at the samples provided with DB2 in the sqllib/samples/c/
or /sqllib/samples/java/ directories for external UDFs.

If you register your function as being DETERMINISTIC (which it hopefully is)
and NO EXTERNAL ACTION (which is also should be), then you can embed the
function in a CHECK constraint and don't have to worry about triggers.

In case you need further help, just let us know...

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message
E.Bartosiewicz@gmail.com - 17 Dec 2007 12:44 GMT
> E.Bartosiew...@gmail.com wrote:
> >> E.Bartosiew...@gmail.com wrote:
[quoted text clipped - 30 lines]
>
> In case you need further help, just let us know...

Ok, so just one last question :)

I also thought of writing the function in c or java as an external
function, which would make things much simpler, but I was asked to
write the check in sql. Is there a chance of doing that and not using
external UDFs?

Ewa
Reply to this Message
Knut Stolze - 17 Dec 2007 20:23 GMT
>> E.Bartosiew...@gmail.com wrote:
>> >> E.Bartosiew...@gmail.com wrote:
[quoted text clipped - 38 lines]
> write the check in sql. Is there a chance of doing that and not using
> external UDFs?

Yes, of course.  SQL is computationally complete, so you can basically do
everything with it.  Based on your description and Lennart's help, I think
you should quickly be able to work out the remaining details.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message
Tonkuma - 18 Dec 2007 05:58 GMT
> Yes, of course.  SQL is computationally complete, so you can basically do
> everything with it.  Based on your description and Lennart's help, I think
[quoted text clipped - 4 lines]
> DB2 z/OS Utilities Development
> IBM Germany- Hide quoted text -

I feel that it is not so easy. The main reasons are followings.
(1) Alphabetical characters would appear not only in left 2 characters
but also other positions
(See second example of ISIN "TREASURY CORP VICTORIA 5 3/4% 2005-2016:
ISIN AU0000XVGZA3").
So, the digits to multply 2 are shifted by the alphabetical characters
right to the digit.
(2) SQL UDFs cannot be used in CHECK condition(I saw DB2 Version 9 SQL
Reference Volume 2 ---> CREATE TABLE).

My trial expression got very complex....
------------------------- Commands Entered -------------------------
SELECT isin
,CAST(MOD(10-MOD(
LOCATE(SUBSTR(isin, 1,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
2,10),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 2,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 3,
9),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 3,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 4,
8),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 4,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 5,
7),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 5,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 6,
6),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 6,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 7,
5),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 7,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 8,
4),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 8,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 9,
3),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 9,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,10,
2),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin,10,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,11,
1),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin,11,1),'   5A  1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4

+LOCATE(SUBSTR(isin, 1,1),'   1   2A  3B  4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S   T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
2,10),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 2,1),'   1   2A  3B  4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S   T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 3,
9),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 3,1),'   1   2A  3B  4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S   T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 4,
8),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 4,1),'   1   2A  3B  4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S   T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 5,
7),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 5,1),'   1   2A  3B  4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S   T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 6,
6),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 6,1),'   1   2A  3B  4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S   T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 7,
5),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 7,1),'   1   2A  3B  4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S   T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 8,
4),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 8,1),'   1   2A  3B  4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S   T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 9,
3),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 9,1),'   1   2A  3B  4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S   T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,10,
2),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin,10,1),'   1   2A  3B  4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S   T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,11,
1),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
,10),10) AS CHAR(1) )
FROM (VALUES 'US037833100', 'AU0000XVGZA', 'GB000263494') S(isin);
--------------------------------------------------------------------

ISIN        2
----------- -
US037833100 5
AU0000XVGZA 3
GB000263494 6

 3 record(s) selected.

Test data were extracted from the reference in Ewa Date: Mon, 17 Dec
2007 05:15:04 -0800 (PST).
Apple Inc.: ISIN US0378331005,
TREASURY CORP VICTORIA 5 3/4% 2005-2016: ISIN AU0000XVGZA3
BAE Systems: ISIN GB0002634946,
Reply to this Message
Knut Stolze - 18 Dec 2007 11:15 GMT
>> Yes, of course.  SQL is computationally complete, so you can basically do
>> everything with it.  Based on your description and Lennart's help, I
[quoted text clipped - 4 lines]
> (2) SQL UDFs cannot be used in CHECK condition(I saw DB2 Version 9 SQL
> Reference Volume 2 ---> CREATE TABLE).

I have used external UDFs in CHECK constraints before.  That's why I assumed
that SQL UDFs can be used in the same way.  But apparently, I was mistake
and there is indeed such a restriction.  So triggers are the way to go
here.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message
Lennart - 18 Dec 2007 13:06 GMT
> > Yes, of course.  SQL is computationally complete, so you can basically do
> > everything with it.  Based on your description and Lennart's help, I think
[quoted text clipped - 16 lines]
>
> My trial expression got very complex....

Hmm, yes you are right. This gets nastier than I thought at first
sight. Second thought was to use a generated column for the
transformation, and a check constraint on that, but checks cant be
used on generated columns (other than identity).

Speaking of which, in V9 and V9.5 docs it is stated that:

The search-condition cannot contain any of the following (SQLSTATE
42621):
[...]
   * Column functions
   * Functions that are not deterministic
   * Functions defined to have an external action
   * User-defined functions defined with either CONTAINS SQL or READS
SQL DATA
[...]

How come:

create table T (c1 char(12) not null);
alter table T add constraint X check ( substr(c1,1,1) = 'A' );

is valid? Isn't substr a column function?

/Lennart
Reply to this Message
Knut Stolze - 18 Dec 2007 14:10 GMT
> The search-condition cannot contain any of the following (SQLSTATE
> 42621):
[quoted text clipped - 12 lines]
>
> is valid? Isn't substr a column function?

No, SUBSTR is a scalar function.  Column functions are aggregate functions
like MIN, MAX, AVG, STDDEV, etc.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message
Lennart - 18 Dec 2007 14:47 GMT
[...]

> No, SUBSTR is a scalar function.  Column functions are aggregate functions
> like MIN, MAX, AVG, STDDEV, etc.

Ah, yes of course. Thanks

/Lennart
Reply to this Message
E.Bartosiewicz@gmail.com - 20 Dec 2007 13:15 GMT
> > No, SUBSTR is a scalar function.  Column functions are aggregate functions
> > like MIN, MAX, AVG, STDDEV, etc.

Thanks, everyone, for the discussion and lots of useful info :)

Ewa
Reply to this Message
Tonkuma - 20 Dec 2007 13:44 GMT
A little shorter solution than my previous answer.
------------------------------ Commands Entered
------------------------------
SELECT isin
,CAST(MOD(10-MOD(
LOCATE(SUBSTR(isin,1,1)
      ,SUBSTR('0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ*S***T'
             ,1+45*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
2,10),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,2,1)
      ,SUBSTR('0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ*S***T0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY'
             ,1+52*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
3,9),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,3,1)
      ,SUBSTR('0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ*S***T'
             ,1+45*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
4,8),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,4,1)
      ,SUBSTR('0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ*S***T0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY'
             ,1+52*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
5,7),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,5,1)
      ,SUBSTR('0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ*S***T'
             ,1+45*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
6,6),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,6,1)
      ,SUBSTR('0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ*S***T0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY'
             ,1+52*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
7,5),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,7,1)
      ,SUBSTR('0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ*S***T'
             ,1+45*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
8,4),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,8,1)
      ,SUBSTR('0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ*S***T0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY'
             ,1+52*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
9,3),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,9,1)
      ,SUBSTR('0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ*S***T'
             ,1+45*MOD(LENGTH(LTRIM(RTRIM(TRANSLATE(SUBSTR(isin,
10,2),'','0123456789')))),2)) )/4
+LOCATE(SUBSTR(isin,10,1)
      ,SUBSTR('0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ*S***T0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY'
             ,CASE WHEN SUBSTR(isin,11,1) BETWEEN '0' AND '9' THEN 1
ELSE 53 END) )/4
+LOCATE(SUBSTR(isin,
11,1),'0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO**TY')/4
,10),10) AS CHAR(1) )
FROM (VALUES 'US0378331005', 'AU0000XVGZA3', 'GB0002634946') S(isin)
;
------------------------------------------------------------------------------

ISIN         2
------------ -
US0378331005 5
AU0000XVGZA3 3
GB0002634946 6

 3 record(s) selected.
Reply to this Message
Lennart - 17 Dec 2007 11:43 GMT
On Dec 17, 10:44 am, E.Bartosiew...@gmail.com wrote:
> > If rather complicated means complicated but concern only this table, I
> > would still aim for check constraint
[quoted text clipped - 5 lines]
> checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
> 1986)

Can you be more specific on what you are trying to do, and why you
need to loop to validate a checksum?

/Lennart
Reply to this Message
E.Bartosiewicz@gmail.com - 17 Dec 2007 13:15 GMT
> On Dec 17, 10:44 am, E.Bartosiew...@gmail.com wrote:
>
[quoted text clipped - 10 lines]
> Can you be more specific on what you are trying to do, and why you
> need to loop to validate a checksum?

I have a string type of data which consists of numbers and letters (an
ISIN -> http://en.wikipedia.org/wiki/International_Securities_Identifying_Number).
I need to change every letter into a numeric equivalent and then do
some arithmetic calculations to get the checksum.

Ewa
Reply to this Message
Lennart - 17 Dec 2007 13:46 GMT
On Dec 17, 2:15 pm, E.Bartosiew...@gmail.com wrote:

> > On Dec 17, 10:44 am, E.Bartosiew...@gmail.com wrote:
>
[quoted text clipped - 15 lines]
> I need to change every letter into a numeric equivalent and then do
> some arithmetic calculations to get the checksum.

I see. IMO it should be possible to do this without a loop. I don't
have the time at the moment (I can see what you think here :-), but I
can provide a solution for a similar problem, which might give you an
idea:

http://en.wikipedia.org/wiki/National_identification_number#Sweden

Since the Y2K 12 positions is used in all system that I know of. The
12 position is a check digit that is calculated as:

2*first position + 1 * second position + 2*third pos + etc

In addition the 9:th pos can contain letters and those are treated as
1. It is used for temporary personal'ids. A check constraint that
validates such number may look like:

ALTER TABLE T ADD CONSTRAINT X
    coalesce(nullif(10 -
    mod(
        2*int(substr(person_id,3,1)) -
                case when 2*int(substr(person_id,3,1)) > 9
                then 9 else 0 end +
        int(substr(person_id,4,1)) +
        2*int(substr(person_id,5,1)) -
                case when 2*int(substr(person_id,5,1)) > 9
                then 9 else 0 end +
        int(substr(person_id,6,1)) +
        2*int(substr(person_id,7,1)) -
                case when 2*int(substr(person_id,7,1)) > 9
                then 9 else 0 end +
        int(substr(person_id,8,1)) +
        case when substr(person_id,9,1) not between '0' and '9'
             then 2
             else 2*int(substr(person_id,9,1)) -
                case when 2*int(substr(person_id,9,1)) > 9
                then 9 else 0
             end
        end +
        int(substr(person_id,10,1)) +
        2*int(substr(person_id,11,1)) -
                case when 2*int(substr(person_id,11,1)) > 9
                then 9 else 0 end,
        10),10),0) = int(substr(person_id,12,1))
);

HTH
/Lennart
Reply to this Message
Lennart - 17 Dec 2007 15:32 GMT
> On Dec 17, 2:15 pm, E.Bartosiew...@gmail.com wrote:
>
[quoted text clipped - 65 lines]
> HTH
> /Lennart

I only had a quick glance at the algorithm, but I assume you would
like a function like:

create function letter2digit (l varchar(1)) returns char(2) return
char(ascii(l) - 55)

which you can apply at your 2 first chars. Your resulting string would
be:

with T (str) as (values 'US0378331005') select letter2digit(substr(str,
1,1)) || letter2digit(substr(str,2,1)) || substr(str,3) from T"

1
----------------
30280378331005

 1 record(s) selected.

From there you should be able to use a similar technique as in my
previous example

HTH
/Lennart
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.