i want to remove string after colon in sql server

Question

-- i need data like this F11.20,F13.20,F14.10 in sql server

declare @S varchar(200) = ',F11.20:,F13.20:Sedative, hypnotic o,F14.10:Cocaine abuse, uncom';
select left(@S, charindex(':', @S, charindex(':', @S)+2)-2);

Answer 1

Any Split/Parse Function would do the trick, but you would have to perform secondary logic to clean the parsed string. That said, I modified a parse function to accept any two non-like delimiters (start/end). In this case a , and :

Also, being a Table-Valued-Function, it is easy to incorporate into a CROSS APPLY or as a stand-alone as illustrated below.

Example

Select NewString = Stuff((Select ',' +RetVal
 From  [dbo].[udf-Str-Extract](@S,',',':')
 For XML Path ('')),1,1,'') 

Returns

F11.20,F13.20,F14.10

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table 
As
Return (  

with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
       cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
       cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
       cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)

Select RetSeq = Row_Number() over (Order By N)
      ,RetPos = N
      ,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1) 
 From  (Select *,RetVal = Substring(@String, N, L) From cte4) A
 Where charindex(@Delimiter2,RetVal)>1

/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[udf-Str-Extract] (@String,'[[',']]')
*/

EDIT Just to Help with the Visualization

If you executed the TVF alone:

declare @S varchar(200) = ',F11.20:,F13.20:Sedative, hypnotic o,F14.10:Cocaine abuse, uncom';

Select * From [dbo].[udf-Str-Extract](@S,',',':')

Returns

RetSeq  RetPos  RetVal
1       2       F11.20
2       10      F13.20
3       38      F14.10

EDIT 2 - Execute via Cross Apply

Declare @YourTable table (ID int,SomeString varchar(200))
Insert Into @YourTable values
(1,',F11.20:,F13.20:Sedative, hypnotic o,F14.10:Cocaine abuse, uncom'),
(2,',Z99.55:,Z25.10:Someother text')

Select A.ID
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select NewString = Stuff((Select ',' +RetVal 
                                           From  [dbo].[udf-Str-Extract](A.SomeString,',',':') 
                                           For XML Path ('')),1,1,'') 
             ) B

Returns

ID  NewString
1   F11.20,F13.20,F14.10
2   Z99.55,Z25.10

Answer 2

If the text you're extracting is always in the form of (letter, number, number, ., number, number) and there's always 3 instances of that text, then you could do this:

WITH 
s1(string, ci) AS (SELECT @S, CHARINDEX(':', @S)),
s2(ci) AS (SELECT CHARINDEX(':', @S, ci+1) FROM s1),
s3(ci) AS (SELECT CHARINDEX(':', @S, ci+1) FROM s2)
SELECT 
  SUBSTRING(string, s1.ci-6, 6)+','+
  SUBSTRING(string, s2.ci-6, 6)+','+
  SUBSTRING(string, s3.ci-6, 6)
FROM s1, s2, s3;

Execution Plan:

It doesn't get any more efficient then that.

If its always the 6 characters before any instance of ":" you can grab a copy of NGrams8K and do this:

declare @S varchar(200) = ',F11.20:,F13.20:Sedative, hypnotic o,F14.10:Cocaine abuse, uncom';

SELECT NewString = STUFF
  ((SELECT ','+SUBSTRING(@S, position-6, 6)
    FROM dbo.NGrams8k(@S, 1)
    WHERE token = ':'
    FOR XML PATH('')),1,1,'');

Another way using NGrams8K and a variable:

declare @S varchar(200) = ',F11.20:,F13.20:Sedative, hypnotic o,F14.10:Cocaine abuse, uncom';
declare @newstring varchar(100)='';

declare @S varchar(200) = ',F11.20:,F13.20:Sedative, hypnotic o,F14.10:Cocaine abuse, uncom';
declare @newstring varchar(100)='';

SELECT 
  @newstring += 
    CASE @newstring WHEN '' THEN '' ELSE ',' END +SUBSTRING(@S, position-6, 6)
FROM dbo.NGrams8k(@S, 1)
WHERE token = ':';

SELECT @newstring;