Substring in sql...

Question

I have a column in SQL table which would have data like this:

"College: Queenstown College" or "University: University of Queensland"

Text before the ":" could be different. So, how can i select only the text before the ":" from the column and text after the ":(space)"?

Answer 1

You should probably consider putting your table into first normal form rather than storing 2 pieces of data in the same column...

;with yourtable as
(
select 'College: Queenstown College' as col UNION ALL
select 'University: University of Queensland'
)
select 
     left(col,charindex(': ',col)-1) AS InstitutionType,
     substring(col, charindex(': ',col)+2, len(col)) AS InstitutionName
from yourtable

Answer 2

Using the charindex and substring functions:

substring(theField, 1, charindex(':', theField) - 1)