How do I write an Oracle SQL query for this tricky question?

Question

Here is the table data with the column name as Ships.

+--------------+
Ships          |
+--------------+
Duke of north  |
---------------+
Prince of Wales|
---------------+
Baltic         |
---------------+

Replace all characters between the first and the last spaces (excluding these spaces) by symbols of an asterisk (*). The number of asterisks must be equal to number of replaced characters.

Answer 1

This really smells like homework. So I won't provide you with the full deal, but point you in the right direction instead:

Check out the function InStr. Espcecially its 3rd and 4th parameters, that allow you to search starting at the Xth char and/or search the Yth occurrence.

Edit: If someone finds this thread in a search and hopes for a solution that works in older versions of Oracle, this is how I'd have done it. (I posted it as a comment to another post, but the author deleted his answer for some inexplicable reason o_O )

SELECT case
         when InStr(Name, ' ', 1) > 0 and
              InStr(Name, ' ', 1) <> InStr(Name, ' ', -1) then
           SubStr(Name, 1, InStr(Name, ' ', 1) - 1) ||
           lPad('*', InStr(Name, ' ', -1) - InStr(Name, ' ', 1) + 1, '*') ||
           SubStr(Name, InStr(Name, ' ', -1) + 1)
         else
           Trim(Name)
       end
FROM   SomeTable

Answer 2

Regular expressions are your friend :)

First match the space, followed by any other characters, ending in a space. Then replace that with a string that consists of the starting and trailing space and, in between, a string of asterisks.

The string of asterisks is made by right padding a single asterisk with further asterisks to the appropriate length. That length is the length of the regular expression matched minus two characters for the leading/trailing space.

select regexp_replace(column_value,' .* ', 
          ' '||rpad('*',length(regexp_substr(column_value,' .* '))-2,'*')||' ')
from table(sys.dbms_debug_vc2coll(
       'Duke of north','Prince of Wales','Baltic','what if two spaces'));

Duke ** north
Prince ** Wales
Baltic
what ****** spaces