Split a column in SELECT

I’m trying to get all records where I have a column concatenated with a :.

SELECT serName FROM tblService WHERE serBqtID=1;

The data stored in table as like this:

serName             serStatus
-------             ---------
catering            Available
Stage               Available
Projector           Available
Segreg:extra        Available
DJ:extra            Available

I want to get below results by removing :extra from column in query:

serName             serStatus
-------             ---------
Segreg              Available
DJ                  Available

I’m not sure what should I do with my above query.

Answer

For SQL Server, you would do:

select left(t.serName, charindex(':', t.serName + ':') - 1) as serName, . . .
from tblService  t
where t.serBqtID = 1 and t.serName like '%:%';

You can find the columns using:

select t.*
from tblService t
where t.serBqtID = 1 and t.serName like '%:%';

The original tag on the question was MySQL, where you can use substring_index():

select substring_index(t.serName, ':', 1) as serName, . . .
from tblService  t
where t.serBqtID = 1 and t.serName like '%:%';