I have a need to clean up some name records in a bunch of tables - over the
years names have been entered in all cap, lower case, mixed case, and some
really strange combinations of punctuation.  This has left us with a mess
when trying to search/match client records by name.

The data in question may be entered as about any format I can think of - and
some I haven't.
 'xxxx' -> 'Xxxx', 'xxxx xxxxx' -> 'Xxxx Xxxxx', 'xxxx xxxx xxxx' -> 'Xxxx
Xxxx Xxxx' etc. - you get the idea. Forcing the text to all upper or lower
case then using brute force with all sorts of LEFT, RIGHT, POSITION,
SUBSTR, LENGTH, etc yields a working result but is it ugly even before I
start testing for existence of error cases!

I could do this simply with a C procedure but I don't intend to maintain
this system much longer (fishing calls!) so an SQL solution would be
preferable for future maintenance reasons. Given the data sources, this
will be an ongoing issue. Can any of you SQL experts suggest a more elegant
solution than my kludge? (or point out where I missed the obvious).

--
Will Honea

The database is on 9.5 at the moment but I have a new server in build with 9.7 so that should
provide the function for a quick and dirty.
If you are not on DB2 9.7 for LUW,

DB2 9.7 for Linux, UNIX, and Windows supports INITCAP function.

DB2 9.7 for Linux, UNIX, and Windows supports INITCAP function.

On 15 Okt, 11:41, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:

DB2 9.7 for Linux, UNIX, and Windows supports INITCAP function.

Tonkuma, I can't seem to find the docs for this function. Can you
provide a link to it?

/Lennart
You can find it by serching "INITCAP" in "IBM DB2 9.7 for Linux, UNIX,

On Oct 16, 2:51 pm, Lennart <erik.lennart.jons... at (no spam) gmail.com> wrote:> On 15 Okt, 11:41, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:

DB2 9.7 for Linux, UNIX, and Windows supports INITCAP function.

Tonkuma, I can't seem to find the docs for this function. Can you
provide a link to it?

/Lennart

You can find it by serching "INITCAP" in "IBM DB2 9.7 for Linux, UNIX,
and Windows Information Center":http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp

On Oct 16, 5:09 am, Will Honea <who... at (no spam) yahoo.com> wrote:
The database is on 9.5 at the moment but I have a new server in build
with 9.7 so that should provide the function for a quick and dirty.
If you are not on DB2 9.7 for LUW,
you can use INITCAP UDF in Sample UDFs for Migration:

http://www.ibm.com/developerworks/data/library/samples/db2/0205udfs/index.html

If you don't have 9.7, is the two-step process of stripping-out non [A-
Z] (and space) characters and then shifting everything to uppercase
more than you want to do?

On Oct 16, 8:22 am, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:

On Oct 16, 2:51 pm, Lennart <erik.lennart.jons... at (no spam) gmail.com> wrote:> On 15 Okt, 11:41, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:

DB2 9.7 for Linux, UNIX, and Windows supports INITCAP function.

Tonkuma, I can't seem to find the docs for this function. Can you
provide a link to it?

/Lennart

You can find it by serching "INITCAP" in "IBM DB2 9.7 for Linux, UNIX,
and Windows Information Center":http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp

The direct link is here:http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...

Henrik

jefftyzzer wrote:
If you don't have 9.7, is the two-step process of stripping-out non [A-
Z] (and space) characters and then shifting everything to uppercase
more than you want to do?

Yep, since the 9.7 INITCAP() function does exactly what I want with no
hassle.

--
Will Honea