FreeAdhocUDF for InterBase and FireBird in deutschin englishen françaisen españolin italianoa português
homeprevious pageÜbersicht • overview • aperçunext pagelast page Seite empfehlen • advice website •Forum mailing-listwas ich vermisse • what I missed •eMail Kommentar • eMail commentprintsitemap
 
Functions enclosed in FreeAdhocUDF.dll/.so/.dylib :
Checksum functions 27 functions
common mathematical algorithm
4 functions
employed checksums/checkdigits
23 functions
Output RETURN mechanism if nothing other is published: FREE_IT
TestSQLs with NULL run only in FireBird 2.
  
Checksum functions: common mathematical algorithm
Preliminary note from Wikipedia (http://en.wikipedia.org/wiki/Checksum):
A checksum or hash sum is a fixed-size datum computed from an arbitrary block of digital data for the purpose of detecting accidental errors that may have been introduced during its transmissions or storage. The integrity of the data can be checked at any later time by recomputing the checksum and comparing it with the stored one. If the checksums do not match, the data was certainly altered.
Checksum functions are related to hash functions, fingerprints, randomisation functions, and cryptographic hash functions. However, each of those concepts has different applications and therefore different design goals. Check digits and parity bits are special cases of checksums, appropriate for small blocks of data (such as Social Security numbers, bank account numbers, computer words, single bytes, etc.). Some error-correcting codes are based on special checksums that not only detect common errors but also allow the original data to be recovered in certain cases.
 
F_CROSSSUM function from adhoc
Entrypoint crosssum compatible with UTF-8
Input INTEGER
natural number
Output INTEGER cross sum from input
The cross sum is the addition of the digits of a number.
i.e. 12345: 5+4+3+2+1 = 15
Test-SQL
SELECT 15 AS ISCORRECT, F_CROSSSUM(12345) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CROSSSUM(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_CROSSDIFF function from adhoc
Entrypoint crossdiff compatible with UTF-8
Input INTEGER
natural number
Output INTEGER cross diff from input
The alternate cross sum (also called cross diff) is to alternate addition and subtraction the digits of a number starting right.
i.e. 12345:  5-4+3-2+1 = 3
Test-SQL
SELECT 3 AS ISCORRECT, F_CROSSDIFF(12345) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CROSSDIFF(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_WEIGHTCROSSSUM function from adhoc
Entrypoint weightcrosssum compatible with UTF-8
Input INTEGER
natural number
Output INTEGER weighted cross sum from input
The weighted cross sum is to add all multipled digits of the number with its place value, starting right.
i.e. 12345: 5*1 + 4*2 + 3*3 + 2*4 + 1*5 = 35
Test-SQL
SELECT 35 AS ISCORRECT, F_WEIGHTCROSSSUM(12345) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_WEIGHTCROSSSUM(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ITERATECROSSSUM function from adhoc
Entrypoint iteratecrosssum compatible with UTF-8
Input INTEGER
natural number
Output INTEGER iterate (or single-digit) cross sum from input
Iterate cross sum is to build the cross sum of a number as long as the result is single digit.
i.e. 12345: 5+4+3+2+1 = 15 -> 5+1 = 6
Test-SQL
SELECT 6 AS ISCORRECT, F_ITERATECROSSSUM(12345) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ITERATECROSSSUM(NULL) FROM RDB$DATABASE;
 
Checksum functions: employed checksums/checkdigits
F_ENCRYPTMD5 compatibility to FreeUDFLibC
Entrypoint encryptmd5or compatible with UTF-8
Input CSTRING(128) String to encrypt
Output CSTRING(33) String enrypted with MD5
Attention: the md5-hash from a string from an ISO-database IS NOT THE SAME as a md5-hash from the SAME string from an UTF-8 database! Only with md5-hashes from strings with the same character set (i.e. both from one database) are suitable for identity-checks.
These function never returns <null> because theres is a correct enryption for <null>.
TestSQL
SELECT 'e7d31845480111fdba3316129e166860' AS ISCORRECT, F_ENCRYPTMD5('Pauline') FROM RDB$DATABASE;
SELECT 'd41d8cd98f00b204e9800998ecf8427e' AS ISCORRECT, F_ENCRYPTMD5('') FROM RDB$DATABASE;
nach oben • go top •
Preliminary note to GTIN-Number from Wikipedia (http://en.wikipedia.org/wiki/GTIN):
Global Trade Item Number (GTIN) is an identifier for trade items developed by GS1 (comprising the former EAN International and Uniform Code Council). Such identifiers are used to look up product information in a database (often by inputting the number through a bar code scanner pointed at an actual product) which may belong to a retailer, manufacturer, collector, researcher, or other entity. The uniqueness and universality of the identifier is useful in establishing which product in one database corresponds to which product in another database, especially across organizational boundaries.
GTIN is an "umbrella" term used to describe the entire family of GS1 data structures for trade items (products and services) identification. GTINs may be 8, 12, 13 or 14 digits long, and can be constructed using any of four numbering structures, depending upon the exact application. GTIN-8s will be encoded in an EAN-8 bar code. GTIN-12s may be shown in UPC-A, ITF-14, or GS1-128 bar codes. GTIN-13s may be encoded in EAN-13, ITF-14 or GS1-128 bar codes, and GTIN-14s may be encoded in ITF-14 or GS1-128 bar codes. The choice of bar code will depend on the application; for example, items to be sold at a retail should be marked with EAN-8, EAN-13, UPC-A or UPC-E bar codes.
- EANInternational Article Number (former European Article Number)
- UCC = Uniform Code Council now GS1
- UPC = Universal Product Code
- EPC = elektronic Product Code
- NVE = Number of transport unit
- GRAI = Global Returnable Asset IDentifier
Identifier former identifier
GTIN-14
GTIN-13 EAN·UCC-13, EAN-13
GTIN-12 EAN·UCC-12, UCC-12, UPC
GTIN-8 EAN·UCC-8, EAN-8
A Check Digit Calculator for GTIN-Numbers http://www.gs1.org/productssolutions/barcodes/support/check_digit_calculator.html
nach oben • go top •
F_GTIN8CHECK function from adhoc
Entrypoint gtin8check compatible with UTF-8
F_UPC12CHECK function from adhoc
Entrypoint upc12check compatible with UTF-8
F_GTIN13CHECK function from adhoc
Entrypoint gtin13check compatible with UTF-8
F_GTIN14CHECK function from adhoc
Entrypoint gtin14check compatible with UTF-8
F_NVE18CHECK function from adhoc
Entrypoint nve18check compatible with UTF-8
Input CSTRING(250) number to check as string
Output INTEGER checkdigit from input
Calculating the checkdigit of a GTIN number. Spaces, hyphens and digits more than max. length are ignored.
Test-SQL
SELECT 0 AS ISCORRECT, F_GTIN8CHECK('1234567') FROM RDB$DATABASE;
SELECT 0 AS ISCORRECT, F_GTIN8CHECK('1-2a3b4c5d6e7fxx999') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_GTIN8CHECK(NULL) FROM RDB$DATABASE;
SELECT 2 AS ISCORRECT, F_UPC12CHECK('12345678901') FROM RDB$DATABASE;
SELECT 7 AS ISCORRECT, F_GTIN13CHECK('762220000460') FROM RDB$DATABASE;
SELECT 8 AS ISCORRECT, F_GTIN14CHECK('0123456789012') FROM RDB$DATABASE;
SELECT 0 AS ISCORRECT, F_NVE18CHECK('01234567890123456') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_GTIN8CHECK(NULL') FROM RDB$DATABASE;
nach oben • go top •
F_GTIN8 function from adhoc
Entrypoint gtin8 compatible with UTF-8
F_UPC12 function from adhoc
Entrypoint upc12 compatible with UTF-8
F_GTIN13 function from adhoc
Entrypoint gtin13 compatible with UTF-8
F_GTIN14 function from adhoc
Entrypoint gtin14 compatible with UTF-8
F_NVE18 function from adhoc
Entrypoint nve18 compatible with UTF-8
Input CSTRING(250) number to check as string
Output CSTRING(8...18) complete number include checkdigit
Complete number include checkdigit. Spaces, hyphens and digits more than max. length are ignored.
Test-SQL
SELECT '12345670' AS ISCORRECT, F_GTIN8('1234567') FROM RDB$DATABASE;
SELECT '123456789012' AS ISCORRECT, F_UPC12('12345678901') FROM RDB$DATABASE;
SELECT '7622200004607' AS ISCORRECT, F_GTIN13('762220000460') FROM RDB$DATABASE;
SELECT '01234567890128' AS ISCORRECT, F_GTIN14('0123456789012') FROM RDB$DATABASE;
SELECT '012345678901234560' AS ISCORRECT, F_NVE18('01234567890123456') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_GTIN8(NULL') FROM RDB$DATABASE;
nach oben • go top •
Preliminary note to ISBN-Number from Wikipedia (http://en.wikipedia.org/wiki/ISBN):
The International Standard Book Number (ISBN) is a unique, numerical commercial book identifier, based upon the 9-digit Standard Book Numbering (SBN) code created by Gordon Foster, now Emeritus Professor of Statistics at Trinity College, Dublin  for the booksellers and stationers W.H. Smith and others in 1966. The 10-digit International Standard Book Number (ISBN) format was developed by the International Organization for Standardization and published as an international standard, ISO 2108, in 1970. (However, the 9-digit SBN code was used in the UK until 1974.) Currently, the ISO TC 46/SC 9 is responsible for the standard.
Since 1 January 2007, International Standard Book Numbers have been of 13 digits, compatible with Bookland EAN-13s.
For other forms of publications, like periodicals or music there are own number systems:
    - ISAN – International Standard Audiovisual Number
    - ISMN – Internationale Standard-Music-Number (for printed or digital musical notations)
    - ISRC – The International Standard Recording Code
    - ISRN – International Standard Technical Report Number
    - ISSN – International Standard Serial Number (for periodicals)
    - ISWC – International Standard Musical Work Code
Publisher identification code numbers are unlikely to be the same in the "978" and "979" ISBNs, like-wise, there is no guarantee that language area code numbers will be the same. Moreover, the ten-digit ISBN check digit generally is not the same as the thirteen-digit ISBN check digit. Because the EAN/UCC-13 is part of the Global Trade Item Number (GTIN) system (that includes the EAN/UCC-14, the UPC-12, and the EAN-8), it is expected that ISBN-generating software should accommodate fourteen-digit ISBNs.
nach oben • go top •
F_ISBN10CHECK function from adhoc
Entrypoint isbn10check
compatible with UTF-8
Input CSTRING(250)
number to check as string
Output INTEGER checkdigit from input
F_ISBN10 function from adhoc
Entrypoint isbn10 compatible with UTF-8
Input CSTRING(250)
number to check as string
Output CSTRING(13) complete, formated ISBN-10 number include checkdigit
Fixed length of 10 characters (with hyphens 13). Allowed characters are 0-9, all other characters and all characters more than length 8 will be taken away.
Test-SQL
SELECT 3 AS ISCORRECT, F_ISBN10CHECK('3-88229-192') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ISBN10CHECK(NULL) FROM RDB$DATABASE;
SELECT '3-88229-192-3' AS ISCORRECT, F_ISBN10('3-88229-192') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ISBN10(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ISBN13CHECK function from adhoc
Entrypoint isbn13check
compatible with UTF-8
Input CSTRING(250)
number to check as string
Output INTEGER checkdigit from input
F_ISBN13 function from adhoc
Entrypoint isbn13 compatible with UTF-8
Input CSTRING(250)
number to check as string
Output CSTRING(17) complete, formated ISBN-13 number include checkdigit
Fixed length of 13 characters (with hyphens 17). Allowed characters are 0-9, all other characters and all characters more than length 13 will be taken away.
Test-SQL
SELECT 6 AS ISCORRECT, F_ISBN13CHECK('978-3-88229-192') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ISBN13CHECK(NULL) FROM RDB$DATABASE;
SELECT '978-3-88229-192-6' AS ISCORRECT, F_ISBN13('978-3-88229-192') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ISBN13(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ISSN8CHECK function from adhoc
Entrypoint issn8check
compatible with UTF-8
Input CSTRING(250)
number to check as string
Output INTEGER checkdigit from input
F_ISSN8 function from adhoc
Entrypoint issn8 compatible with UTF-8
Input CSTRING(250)
number to check as string
Output CSTRING(9) complete, formated ISSN-8 number include checkdigit
Fixed length of 8 characters (with hyphen 9). Allowed characters are 0-9, all other characters and all characters more than length 8 will be taken away.
Test-SQL
SELECT 9 AS ISCORRECT, F_ISSN8CHECK('0724-867') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ISSN8CHECK(NULL) FROM RDB$DATABASE;
SELECT '0724-8679' AS ISCORRECT, F_ISSN8('0724-867') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ISSN8(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_UPSTNCHECK function from adhoc
Entrypoint upstncheck
compatible with UTF-8
Input CSTRING(250)
number to check as string
Output INTEGER checkdigit from input
F_UPSTN function from adhoc
Entrypoint upstn compatible with UTF-8
Input CSTRING(250)
number to check as string
Output CSTRING(18) complete, formated UPS tracking number include checkdigit
UPS Tracking-Nummer.
Fixed length of 18 characters. Allowed characters are 0-9 and A-Z, all other characters and all characters more than length 18 will be taken away.
Test-SQL
SELECT 6 AS ISCORRECT, F_UPSTNCHECK('1Z 591580 68 5558773') FROM RDB$DATABASE;
SELECT 6 AS ISCORRECT, F_UPSTNCHECK('591580685558773') FROM RDB$DATABASE;
SELECT 6 AS ISCORRECT, F_UPSTNCHECK('59158068555877311111111') FROM RDB$DATABASE;
SELECT 3 AS ISCORRECT, F_UPSTNCHECK('1Z 0F6915 68 2239322') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_UPSTNCHECK(NULL) FROM RDB$DATABASE;
SELECT '1Z 591580 68 55587736' AS ISCORRECT, F_UPSTN('1Z 591580 68 5558773') FROM RDB$DATABASE
SELECT '1Z 0F6915 68 22393223' AS ISCORRECT, F_UPSTN('1Z 0F6915 68 22393223') FROM RDB$DATABASE
SELECT NULL AS ISCORRECT, F_UPSTN(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_2OF5CHECK function from adhoc
Entrypoint twooffivecheck
compatible with UTF-8
Input CSTRING(250)
number to check as string
Output INTEGER checkdigit from input
F_2OF5 function from adhoc
Entrypoint twooffive compatible with UTF-8
Input CSTRING(250)
number to check as string
Output CSTRING(13) complete 2 of 5 number include checkdigit
2 of 5 number.
Variable length. Allowed characters are 0-9, all other characters will be taken away.
Can be used for 2of5 industry and 2of5 interleaved.
Test-SQL
SELECT 5 AS ISCORRECT, F_2OF5CHECK('123456') FROM RDB$DATABASE;
SELECT 5 AS ISCORRECT, F_2OF5CHECK('ABC123456XYZ') FROM RDB$DATABASE;
SELECT 0 AS ISCORRECT, F_2OF5CHECK('1234567') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_2OF5CHECK(NULL) FROM RDB$DATABASE;
SELECT '1234565' AS ISCORRECT, F_2OF5('123456') FROM RDB$DATABASE;
SELECT '12345670' AS ISCORRECT, F_2OF5('1234567') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_2OF5(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_CODE39CHECK function from adhoc
Entrypoint code39check
compatible with UTF-8
Input CSTRING(250)
number to check as string
Output INTEGER checkdigit from input
F_CODE39 function from adhoc
Entrypoint code39 compatible with UTF-8
Input CSTRING(250)
number to check as string
Output CSTRING(13) complete code 39 number include checkdigit
Code-39 (3 of 9) number.
Variable length. Allowed characters are 0-9, A-Z and $ % + - . / and space, all other characters will be taken away.
Test-SQL
SELECT 'K' AS ISCORRECT, F_CODE39CHECK('AB-123') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CODE39CHECK(NULL) FROM RDB$DATABASE;
SELECT 'AB-123K' AS ISCORRECT, F_CODE39('AB-123') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CODE39(NULL) FROM RDB$DATABASE;
vorige Seite • previous page • passée sitenach oben • go top • vers le hautnächste Seite • next page • prochain site