Data manipulation

I have a table which contains the following data:

1877: Morell, Prince Edward Island 1878: Morin-Heights, Quebec 1879: Morinville, Alberta 1880: Morley, Alberta

etc.

I would like to extract just the name that is found between (: ) and (,)
Is there a way i could do that?

Thanks in advance

Code Snippet

selectsubstring(data,charindex(':', data)+1,len(data)-charindex(',',reverse(rtrim(data)))-charindex(':', data))

from(

select'1877: Morell, Prince Edward Island 'as data

unionallselect'1878: Morin-Heights, Quebec'

unionallselect'1879: Morinville, Alberta '

unionallselect'1880: Morley, Alberta'

)as x

|||

This could work for you:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyData varchar(50)
)

INSERT INTO @.MyTable VALUES ( '1877: Morell, Prince Edward Island ' )
INSERT INTO @.MyTable VALUES ( '1878: Morin-Heights, Quebec' )
INSERT INTO @.MyTable VALUES ( '1879: Morinville, Alberta' )
INSERT INTO @.MyTable VALUES ( '1880: Morley, Alberta' )

SELECT substring( MyData, ( charindex( ': ', MyData ) + 2 ), ( charindex( ', ', MyData ) - charindex( ': ', MyData ) - 2 ))
FROM @.MyTable

--
Morell
Morin-Heights
Morinville
Morley

|||Thank you