how to ignore first word in a string

[arage]

how to ignore first word in a string
my personal database I’m working on contains book titles & in my asp web page I’d like the user selection of a particular letter A-Z to bring up appropriate titles, but the problem is if the title of the story is:

A DARK STORMY NIGHT

It should not appear when a user chooses the A section from my combo box, a title like that should skip A (or THE0 and use the next letter as the section where the book should appear, in my case above it should appear under the D section. I guess I’m wondering what function I ought to include in my SQL string perhaps?

[jmodic]

If I understand you correctly, you don't want to ignore *every* first word, but only some pecific examples, like 'A ' and 'THE ' and perhaps a few others.

The best way for this would be to search directly for the occurance of those words, for example with INSTR() OR SUBSTR() functionS. To check for more than one different word to ignore you can either use DECODE or CASE.

Example with DECODE and INSTR():

SELECT DECODE(1,
INSTR(title, 'A '), SUBSTR(title, 3),
INSTR(title, 'THE '), SUBSTR(title, 5),
title) trimmed_title
FROM my_titles;

Example with CASE and SUBSTR()

SELECT CASE
WHEN SUBSTR(title, 1, 2) = 'A ' THEN SUBSTR(title, 3)
WHEN SUBSTR(title, 1, 4) = 'THE ' THEN SUBSTR(title, 5)
ELSE title
END) trimmed_title
FROM my_titles;

[Jani]

Hi
In which version of oracle we use CASE , WHEN and THEN ?
Thanx

[nealh]

Oracle 8i for support in ordinary SQL but not in PL/SQL.

Oracle 9 for full support.

[Jani]

I m using 8i 8.1.5 when i run the statement

SQL> select * from ac;
NAME
--------------------
A ABAABANBAAA
A HDGJHGSDHGSJ
THE HDGJHGSDHGSJ

------------------ And when I run

SQL> SELECT CASE
WHEN SUBSTR(name, 1, 2) = 'A ' THEN SUBSTR(name, 3)
WHEN SUBSTR(name, 1, 4) = 'THE ' THEN SUBSTR(name, 5)
ELSE name
END) trimmed_title
FROM ac

Error
=======

WHEN SUBSTR(name, 1, 2) = 'A ' THEN SUBSTR(name, 3)
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

Is thier any need of script files ?
or
How I can solve this problem ?
Thanx

[nealh]

You have an unnecessary trailing bracket:

SELECT CASE
WHEN SUBSTR(name, 1, 2) = 'A ' THEN SUBSTR(name, 3)
WHEN SUBSTR(name, 1, 4) = 'THE ' THEN SUBSTR(name, 5)
ELSE name
END trimmed_title
FROM ac

or

SELECT (CASE
WHEN SUBSTR(name, 1, 2) = 'A ' THEN SUBSTR(name, 3)
WHEN SUBSTR(name, 1, 4) = 'THE ' THEN SUBSTR(name, 5)
ELSE name
END) trimmed_title
FROM ac

[Jani]

Sorry Dear
Both or not working here !

SQL> SELECT CASE
WHEN SUBSTR(name, 1, 2) = 'A ' THEN SUBSTR(name, 3)
WHEN SUBSTR(name, 1, 4) = 'THE ' THEN SUBSTR(name, 5)
ELSE name
END trimmed_title
FROM ac

SQL> /
WHEN SUBSTR(name, 1, 2) = 'A ' THEN SUBSTR(name, 3)
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

============================
SQL> SELECT (CASE
WHEN SUBSTR(name, 1, 2) = 'A ' THEN SUBSTR(name, 3) WHEN SUBSTR(name, 1, 4) = 'THE ' THEN SUBSTR(name, 5) ELSE name
END) trimmed_title
FROM ac
==============================
WHEN SUBSTR(name, 1, 2) = 'A ' THEN SUBSTR(name, 3)
*
ERROR at line 2:
ORA-00907: missing right parenthesis

[nealh]

It works on my database, which is 8.1.6. Must have come in with that release.

[sarath]

Hi
try this one
1* select substr(' A DARK STORMY NIGHT',instr(ltrim(' A DARK STORMY NIGHT',' '
SCOT>/

SUBSTR('ADARKSTORM
------------------
DARK STORMY NIGHT

Sarath.