CODEWITHSUNDEEP
sqlsql-server
user1773603
user1773603

Reputation:

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.

Upvotes: 1

Views: 70

Answers (2)

Radu
Radu

Reputation: 1031

For MS-SQL use:

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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 '%:%';

Upvotes: 0

Related Questions