Need help on this SQL...

Question

Here is the question:

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

I need to 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.

for example: "Duke of North" must be replaced as "Duke ** North", I managed to cut the "of" from "Duke of North" but am really stuck with the replacement part, can any one give me some ideas, am learning SQL in MS SQL Server 2005, please help. I dont need the exact answer just give me some ideas,

select 
case when charindex(' ',ship,(charindex(' ',ship,1))+1) > 0 
     then substring(ship,(charindex(' ',ship,1))+1,((charindex(' ',ship,(charindex(' ',ship,1))+1)) - (charindex(' ',ship,1))))
end "Replace"
from 
outcomes

This is not a homework question, am learning SQL.

Answer 1

Part of learning SQL is to learn to identify tasks that should not be performed in SQL. This should be left as part of the display logic, and should stay out of the database engine.

Answer 2

select 
isnull(case when charindex(' ',ship,(charindex(' ',ship,1))+1) > 0 
     then substring(ship,1,charindex(' ',ship,1))+space(1)+replace(substring(ship,(charindex(' ',ship,1))+1,((charindex(' ',ship,(charindex(' ',ship,1))+1)) - (charindex(' ',ship,1)))),substring(ship,(charindex(' ',ship,1))+1,((charindex(' ',ship,(charindex(' ',ship,1))+1)) - (charindex(' ',ship,1)))),replicate('*',len(substring(ship,(charindex(' ',ship,1))+1,(charindex(' ',ship,(charindex(' ',ship,1))+1)) - (charindex(' ',ship,1))))))+space(1)+substring(ship,(charindex(' ',ship,(charindex(' ',ship,1))+1)),(len(ship)))
end,ship) "Replace"
from 
outcomes

After a long try got the answer on my own!!!

Answer 3

SELECT 
CASE WHEN ship NOT LIKE '% % %' THEN ship
ELSE
LEFT(ship,CHARINDEX(' ',ship)) + 
      REPLICATE('*', LEN(ship)- CHARINDEX(' ',REVERSE(ship)) - CHARINDEX(' ',ship)) +
      RIGHT(ship, CHARINDEX(' ',REVERSE(ship)))
END
FROM outcomes

If you commonly need to Select the data in this modified form you could add a computed column as below.

ALTER TABLE dbo.outcomes ADD
    displayName  AS CASE WHEN ship NOT LIKE '% % %' THEN ship
ELSE
LEFT(ship,CHARINDEX(' ',ship)) + 
      REPLICATE('*', LEN(ship)- CHARINDEX(' ',REVERSE(ship)) - CHARINDEX(' ',ship)) +
      RIGHT(ship, CHARINDEX(' ',REVERSE(ship)))
END PERSISTED