String functions in SQL
in [DB2]
|
From: Will Honea on 15 Oct 2009 01:38 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
From: Tonkuma on 15 Oct 2009 05:41 DB2 9.7 for Linux, UNIX, and Windows supports INITCAP function.
From: jefftyzzer on 15 Oct 2009 13:55 On Oct 14, 10:38 pm, Will Honea <who...(a)yahoo.com> wrote: > 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 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? -Jeff
From: Will Honea on 15 Oct 2009 16:09 Tonkuma wrote: > DB2 9.7 for Linux, UNIX, and Windows supports INITCAP function. Thank you sir, that does indeed solve the problem. 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. Looks like I was re-inventing the wheel. -- Will Honea
From: Tonkuma on 15 Oct 2009 18:14
On Oct 16, 5:09 am, Will Honea <who...(a)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 |