Salutations Used in Direct Mail

I am working on a direct mail project that requires personalized letters addressed to all manner of people from around the world - from members of Congress to Doctors, lawyers, members of parliament, etc. and these letters are being translated into multiple languages. I am searching for an already existing table of translated titles of address or salutations.

The following pages include fairly complete lists of salutations but I am hunting around for more complete and then translated tables like this. If anyone out there knows of such a table, please let me know.

Table with Forms of Address
Official Forms of Address

Char_Instr - Another Matching Algorithm

I've using Soundex, Double Metaphone, Levenshtein Distance and other string matching algorithms, but somethings it is the simple things that work best.

Here is a function I wrote in PL/SQL utilizing the INSTR() function to traverse through one string, checking each character as to whether or not it exists in the another string.

This has been useful in checking for duplicates in mailing lists as well as identifying potential duplicates that should be reviewed. The function returns the number of characters from the second string that are the same as the first string.

I've found the use of the Soundex to be too loose and the Levenshtein Distance algorithm to be lacking in its handling of transposed characters. Use of this function will give you a count of how many chars are the same regardless of position in the string.


/*==========================================================
Function: char_instr (str1, str2)

Purpose: To check how many characters from one string
exist in another string. The return value is the number
of characters that exist in the in both strings.
==========================================================*/

FUNCTION char_instr
(
str1 IN VARCHAR2,
str2 IN VARCHAR2
)
RETURN NUMBER
AS
v_exists NUMBER := 0;
v_occurs NUMBER := 0;
v_length NUMBER;
BEGIN
/*==========================================================
Get the length of the str2
==========================================================*/
v_length := LENGTH(str2);
/*==========================================================
Loop through str2 and check if char exists in str1
=========================================================*/
IF( v_length > 0 ) THEN
FOR x IN 1..v_length LOOP
v_exists := INSTR(UPPER(str1), SUBSTR(UPPER(str2), x, 1));
IF(v_exists>0) THEN
v_occurs := v_occurs + 1;
END IF;
END LOOP;
END IF;
RETURN v_occurs;
END char_instr;
*

SQL Cheat Sheet

I've put together this page to give quick links to all the Oracle SQL tricks that I find myself continuing to search for on the net. This is a quick reference guide and will be expanded regularly.

Oracle SQL Cheat Sheet

SQL Introduction and Definitions

INSTR()

Create a Delimited Output in SQL Plus

How to Rename a Column in Oracle SQL

How to Delete a Column from a Table

Use of Variables in SQL Plus or PL/SQL

Format Query Results to Display Currency

Display Seconds Past Midnight

Select Only Top Results in a Query

Use of Wildcards in Oracle SQL

What is NVL and how does it work?

How to Use Decode in Oracle SQL

How to Round Off a Number in Oracle SQL

Debug JDBC Connectivity with Oracle Thin Client

Oracle String Matching Algorithm - CHAR_INSTR

If you have any other tips or suggested entries, let me know!

Nice Bike Tom!

Check out these shots of Tom Cruise's new bike. I hear it has a Ducati engine and was custom built for him, sized to fit.

Standard Mail $.02-$.03 rate reduction proposed by PRC

NOTE: THIS IS ONLY PROPOSED AT THIS POINT - NOT YET APPROVED

This morning I received the following pleasant news update from the DMA regarding a temporary rate reduction proposed on all Standard USPS mail:

The Postal Regulatory Commission May 25 offered interim rate relief for Standard Regular flats and catalog mailers in response to the U.S. Postal Service Governors’ request to reconsider the recommended rates in March.

The PRC’s decision grants a temporary rate reduction of 3 cents for all Standard Mail regular flats and 2 cents for Standard Regular nonprofit flats. The temporary transitional rate relief for Standard Regular flats mailers would end Sept. 29.

click to read full story...

They would not require any software change be made but simply a blanket 3 cents per Standard Mail Flat and 2 cents per Non-Profit Standard Mail Flat to be deducted from the overall postage statement.

Mailers concerned should contact their USPS postal reps and support this PRC recommendation.

How to Round Up in Oracle SQL

Recently I needed to provide a report that had numbers rounded to the nearest thousand. I found the ROUND command but this appeared to be only rounding off decimal points to a specified number of spaces. The syntax for this is:

SELECT ROUND(number, decimals)
FROM table;

If you use 2 for the decimals your result would look like this:

SELECT ROUND(2.123,2) FROM DUAL;
Result: 2.12
SELECT ROUND(2.129,2) FROM DUAL;
Result: 2.13

However, I needed something that would round to the nearest thousandth. So I tried a negative number for the decimals like this and found it did exactly what I wanted:

SELECT ROUND(10234,-3) FROM DUAL;
Result: 10000
SELECT ROUND(10567,-3) FROM DUAL;
Result: 11000

While this is a basic Oracle SQL function, it is not clear that you can do this, hence this post.

USPS Rate Change - What do you think?

May 14th, 2006 will go down in history as the most confusing day in mailers history. Unfortunately many people did not install the new presorting software on development machines and get them fully tested before the big date and tried to frantically switch over on May 14th. The tech support team at Firstlogic was overwhelmed with calls - specifically in their Presort support dept. Complaints regarding the post office rejecting their mail, confusing new requirements and general upset due to the increase in postage as a result of the new rate case. Mailers are experiencing anywhere upwards of $.10 per piece increase - the highest increase in standard mail that I can remember.

I saw a post on the DM News website that the USPS claimed the new rate case implementation was going smooth - however it is quite different on the ground in speaking to mailers and software support technicians.

Per the below excerpt from DM News page, it looks as though the Postal Regulation Committee is considering reverting the Standard Flat Mail rate change due to the "rate shock" occurring:

The Governors of the USPS asked that the rates for this category of mail be reconsidered because the price increases recommended by the PRC may impose an unnecessary degree of “rate shock” on the catalog industry and small businesses particularly. The recommended increase for some catalog mailers is as much as 40 percent, which is more than double what the Postal Service had proposed.

Here is a poll to find out your opinion of the new rate case. Please vote and let me know your comments on this.