Validate account number SQL table

I'd like to select data from a table with the following rule, but I'm having trouble writing the query. I'm using PostgreSQL and I can't create a UDF. The table looks like this:

id  | user_id  | account_number
-------------------------------
 1  |    1     | 12345671
 2  |    4     | 12356673
 3  |    7     | 12325678

The id and user_id are integers whereas the account number is a string. I'd like to select the account numbers that match the following conditions:

• Account number string contains exactly 8 digits
• Validation scheme
  1. Take the first 7 digits
  2. Multiply the first digit by 1, the second by 2, the third by 3, the fourth by 1, the fifth by 2, the sixth by 3 and the seventh by 1
  3. Sum the result of multiplying each digit by the relevant number
  4. If the 8th digit is the same as mod(sum, 10) then select this number
  开发者_Go百科

In this table above, I should only return the first two rows with the query.

Just to repeat, I can't create a UDF, so am looking to find out whether this is possible using ordinary SQL in a query.

Thanks!

Yes, you can do it. Basically, use SIMILAR TO to check for exactly 8 digits, then substring and cast to do the math. Something like this:

SELECT * FROM table_name WHERE
  account_number SIMILAR TO '[0-9]{8}'
  AND (
    1 * CAST(SUBSTR(account_number, 1, 1) AS INTEGER) +
    2 * CAST(SUBSTR(account_number, 2, 1) AS INTEGER) +
    3 * CAST(SUBSTR(account_number, 3, 1) AS INTEGER) +
    1 * CAST(SUBSTR(account_number, 4, 1) AS INTEGER) +
    2 * CAST(SUBSTR(account_number, 5, 1) AS INTEGER) +
    3 * CAST(SUBSTR(account_number, 6, 1) AS INTEGER) +
    1 * CAST(SUBSTR(account_number, 7, 1) AS INTEGER)
  )%10 = CAST(SUBSTR(account_number, 8, 1) AS INTEGER)

Of course, this returns no rows in your example, because:

1×1 + 2×2 + 3x3 + 1×4 + 2×5 + 3×6 + 1×7 = 53
53 MOD 10 = 3
3 ≠ 1

PS: You do realize the UDFs can be written in languages other than C. E.g., you can write one in PL/pgSQL.

Just create a table where you split out your digits, then do the arithmetic (of course, you can fill in the rest of the stuff where the ...s are).

create table digits as
select account_number,
substr(account_number::text,0,1)::int as digit_1
,substr(account_number::text,1,1)::int as digit_2
,...
substr(account_number::text,7,1) as digit_8
from table
where account_number::text~E'[0-9]{8}'; --regex to make sure acct numbers are of the right form.

select account_number,
(digit_1+2*digit_2+...+3*digit_6+dight_7)%10=digit_8 as valid
from digits;

Of course, if you'd rather not create a separate table, you can always put the select statement for the creation of the digits table into a subquery for the second query.

select id, 
       user_id,
       digit_sum, 
       last_digit 
from (
  select id, 
         user_id,
         (substring(account_number,1,1)::int + 
          substring(account_number,2,1)::int * 2 + 
          substring(account_number,3,1)::int * 3 + 
          substring(account_number,4,1)::int + 
          substring(account_number,5,1)::int * 2 + 
          substring(account_number,6,1)::int * 3 +
          substring(account_number,7,1)::int ) as digit_sum, 
          substring(account_number,8,1)::int as last_digit
  from accounts
) t
where last_digit = digit_sum % 10

To make life easier, I would create a view that does the splitting and summing of the values. Then you just need to select from that view with the where condition I used for the derived table.

You can try something along these lines.

select *,   (
    (substring(account_number from 1 for 1)::integer * 1) +
    (substring(account_number from 2 for 1)::integer * 2) +
    (substring(account_number from 3 for 1)::integer * 3) +
    (substring(account_number from 4 for 1)::integer * 1) +
    (substring(account_number from 5 for 1)::integer * 2) +
    (substring(account_number from 6 for 1)::integer * 3) +
    (substring(account_number from 7 for 1)::integer * 1)
   ) as sums,
  (
    (substring(account_number from 1 for 1)::integer * 1) +
    (substring(account_number from 2 for 1)::integer * 2) +
    (substring(account_number from 3 for 1)::integer * 3) +
    (substring(account_number from 4 for 1)::integer * 1) +
    (substring(account_number from 5 for 1)::integer * 2) +
    (substring(account_number from 6 for 1)::integer * 3) +
    (substring(account_number from 7 for 1)::integer * 1)
   ) % 10 as mod_10
from acct_no
where length(account_number) = 8 

I wrote the calculation into the SELECT clause instead of the WHERE clause, because either my arithmetic is wrong or your specs are wrong.