Need help SELECTing parts of a field

Hi Gweely,

If you only need to find rows containing a search string, then the delimiters
are not really relevant; use the LIKE operator - for example, if the user
wants to search for books to which Bob Frost contributed, he should search
for 'Frost' and your query should use a WHERE clause including something
along the lines of:

AND toUpper(Author) LIKE '%FROST%'

If you also have a table of authors AND the author entries always follow
the same format (looks unlikely given your example), give the user a drop-down
list to select from; then you can search for the whole name as stored:

AND Author LIKE '%Frost, W.B. Robert%'

If you actually need to isolate the names as strings in their own right,
then you will have to write some procedural code (user-defined functions)
in whatever language your platform supports. For example, you might write
a function to return a cursor containing one row for each author, given the
id of the title row.

Another possibility is to create a table of authors and a table of title
/ author cross-references, and use user-defined functions, both to populate
them once, and to maintain them when changes are made to the titles table.
You could then use the authors and authors/titles intersection table in
the searches.

Hope this is some help,
Simon Sellick.

"Gweely" <gillestai@yahoo.com> wrote:
>
>I need help SELECTing English descriptor words that are all kept in the
same
>field of a table separated by a ; and also an Author field with 2-3
authors
>in the same field separated by ; I am creating a search engine for this
Libraby
>database. The people who created the database refuse to let me change the
>format and I have not read anywhere in SQL books or documentation a command
>for a delimiter or to parse the fields at certain characters. I need to
know
>if this is possible or if it is just a dead end. If it is impossible I need
>to find another way of finding an exact word in a field like << plants ;
>desease ; gumming ; food extraction ; recent methods of cropping >> or a
>string lik ethis one << Frost, W.B. Robert ; Nicholas, D.E. Gregory ; Phillip,
>D. Lou ; Zappata, K. Frank >>
>
>I don't quite know how to go about this I am racking my brain and can't
find
>a command that helps with this situation.

"Simon Sellick" <simon.sellick@tesco.net> wrote:
>
>Hi Gweely,
>
>If you only need to find rows containing a search string, then the delimiters
>are not really relevant; use the LIKE operator - for example, if the user
>wants to search for books to which Bob Frost contributed, he should search
>for 'Frost' and your query should use a WHERE clause including something
>along the lines of:
>
> AND toUpper(Author) LIKE '%FROST%'
>
>If you also have a table of authors AND the author entries always follow
>the same format (looks unlikely given your example), give the user a drop-down
>list to select from; then you can search for the whole name as stored:
>
> AND Author LIKE '%Frost, W.B. Robert%'
>
>If you actually need to isolate the names as strings in their own right,
>then you will have to write some procedural code (user-defined functions)
>in whatever language your platform supports. For example, you might write
>a function to return a cursor containing one row for each author, given
the
>id of the title row.
>
>Another possibility is to create a table of authors and a table of title
>/ author cross-references, and use user-defined functions, both to populate
>them once, and to maintain them when changes are made to the titles table.
> You could then use the authors and authors/titles intersection table in
>the searches.
>
>Hope this is some help,
>Simon Sellick.
>
>"Gweely" <gillestai@yahoo.com> wrote:
>>
>>I need help SELECTing English descriptor words that are all kept in the
>same
>>field of a table separated by a ; and also an Author field with 2-3
>authors
>>in the same field separated by ; I am creating a search engine for this
>Libraby
>>database. The people who created the database refuse to let me change the
>>format and I have not read anywhere in SQL books or documentation a command
>>for a delimiter or to parse the fields at certain characters. I need to
>know
>>if this is possible or if it is just a dead end. If it is impossible I
need
>>to find another way of finding an exact word in a field like << plants
;
>>desease ; gumming ; food extraction ; recent methods of cropping >> or
a
>>string lik ethis one << Frost, W.B. Robert ; Nicholas, D.E. Gregory ; Phillip,
>>D. Lou ; Zappata, K. Frank >>
>>
>>I don't quite know how to go about this I am racking my brain and can't
>find
>>a command that helps with this situation.
>