SQL Server Split text

[billy_111]

Hey,

I have an issue, i am selecting something in SQL Server which gives me a value of this:

web_Day_1_Hotel
web_Day_2_Hotel
web_Day_3_Windsor (Boxing Day)
web_Day_4_Henley-on-Thames

This is an example of the type of query i am using:

SELECT Name as name
FROM Note 
FOR XML PATH('item') 

Now what i wanted to know is that if there is a way to display the result as:

Day 1
Day 2
Day 3
Day 4

Any ideas?

Many thanks,

[r937]

SUBSTRING(REPLACE(name,'_',''),5,5)

[billy_111]

Thats perfect, thanks.

Has been a long time since i've posted on this forum as i got a new job. But will be posting on here more often.

Thanks again