Re: SQL Partial "similar-ish" key matching, can it be done?

On Tue, 2015-12-15 at 10:04 -0500, Charles Wilt wrote:

How's this?

select * from qtemp.cntry
where :newcode like (CountryCode concat '%')
or CountryCode like (:newcode concat '%')

Well that was far easier than I had assumed... but leads me on to one
final question.

Given the 10 digit ISBN holds a country code within it, and there is no
indication of how many digits make up the country code... how would I do
something along the lines of:

select SomeFields from BiblFile join with Countryfile where
BiblFile.ISBN contains partial match with CountryFile.Country.

So
011700001X TitleA
810115400X TitleB
922241012X TitleC
922941011X TitleD

and
0 English
1 English
80 Czech Republic
81 India
82 Norway
9229 Guatemala

creates
011700001X TitleA 0 English
810115400X TitleB 1 India
922241012X TitleC
922941011X TitleD 9229 Guatemala

Using your assumption of varying length fields, no trailing spaces.

I get the feeling that its something to do with joining the country file
to the bibliographic file, but in reverse - kind of.

Select SomeFields from BiblFile and CountryFile where rightjoin
(CountryFile.Country *cat '%') like Biblfile.ISBN

Charles


On Tue, Dec 15, 2015 at 9:47 AM, Wilson, Jonathan <piercing_male@xxxxxxxxxxx

wrote:

This question is based in part on an ISBN structure, but is not strictly
following the rules.

Both short and long answers are appreciated, even ones that say "look at
the ... statement in the ... manual" or "this link does something
similar, you'll have to adapt it to DB2/i" as I'm working with very very
little experience of SQL (basic selects/inserts/updates is my current
level of experience).

An ISBN is made up of 3 parts (Excluding the new prefix, and checksum).

Country (Also language, as "English" is denoted by 0 and 1)
Imprint (publisher)
Book

All the parts are variable in length, so if a country produces few books
its country code is longer than a country producing a lot of titles.

<snip>