none
Get a string from a sentence with the occurence of another String RRS feed

  • Question

  •  

    I have a table with a column Col1 which has values as below:

    SELECT 'XXX-T09-CFO Signature' AS  Col1  
    UNION
    SELECT 'YYY-T140 - Update Funding Authorization Status to Completed'
    UNION SELECT'ZZZ-T13-PAB Chair Signature' 

    My output col2 should have string before the word "Signature" from col1.If the word Signature is not found it should be set to NULL
    SELECT 'XXX-T09-CFO Signature' AS  Col1,'CFO' as Col2
    UNION 
    SELECT 'YYY-T140 - Update Funding Authorization Status to Completed' ,Null
    UNION 
    SELECT'ZZZ-T13-PAB Chair Signature','PAB Chair'


     
    Friday, January 10, 2020 5:30 PM

All replies

  •  
    
     
    ;with mycte as ( 
    SELECT Col1, reverse(parsename(replace(col1,'Signature','.')+' ',2))as [Col2]
     from (SELECT 'XXX-T09-CFO Signature' AS  Col1  
    UNION all
    SELECT 'YYY-T140 - Update Funding Authorization Status to Completed'
    UNION all
    SELECT'ZZZ-T13-PAB Chair Signature' ) t 
    )
    
    select Col1,
    Rtrim(Reverse(stuff(Col2,charindex('-',Col2),len(Col2),''))) Col2
    from mycte
     

    Friday, January 10, 2020 6:04 PM
    Moderator
  • ;with cte as
    (
    	SELECT 'XXX-T09-CFO Signature' AS  Col1  
    	UNION
    	SELECT 'YYY-T140 - Update Funding Authorization Status to Completed'
    	UNION 
    	SELECT'ZZZ-T13-PAB Chair Signature' 
    )
    select
    	c.Col1,
    	(case when c.Col1 like '% Signature' then replace(c.Col1, ' Signature','') else null end) as Col2
    from
    	cte c;

    I hope this helps.

    --Dan


    Friday, January 10, 2020 7:41 PM
  • Hi CSKKingsForEver,

    One more version of the search:

    DECLARE @signature CHAR(9) = 'Signature';
    SELECT *
    	, (CASE WHEN Col1 LIKE '%' + @signature THEN REPLACE(Col1, @signature, '') 
    		ELSE NULL END) AS Col2
    	-- case-insensitive search
    	, (CASE WHEN CHARINDEX(@signature, Col1 COLLATE Latin1_General_CI_AS) > 0 THEN REPLACE(Col1, @signature, '') 
    		ELSE NULL END) AS Col3
    FROM 
      (VALUES
        ('XXX-T09-CFO Signature'),
        ('YYY-T140 - Update Funding Authorization Status to Completed'),
        ('ZZZ-T13-PAB Chair Signature')
      ) AS mockTbl(Col1);

    Friday, January 10, 2020 8:35 PM
  • DECLARE @s varchar(100);
    SET @s = 'XXX-T09-CFO Signature';
    --SET @s = 'ZZZ-T13-PAB Chair Signature';
    --SET @s = 'YYY-T140 - Update Funding Authorization Status to Completed';
    
    SELECT CASE WHEN CHARINDEX('Signature', @s) > 0 THEN
    	LTRIM(
    		RTRIM(
    			REVERSE(
    				SUBSTRING(
    					REVERSE(SUBSTRING(@s, 1, CHARINDEX('Signature', @s) - 1)), 
    					1, 
    					CHARINDEX('-', REVERSE(SUBSTRING(@s, 1, CHARINDEX('Signature', @s) - 1))) - 1
    				)
    			)
    		)
    	)
    	ELSE NULL
    	END AS Col2;


    A Fan of SSIS, SSRS and SSAS

    Friday, January 10, 2020 10:09 PM
  • WITH CTE AS (
    	SELECT 'XXX-T09-CFO Signature' AS  Col1  
    	UNION
    	SELECT 'YYY-T140 - Update Funding Authorization Status to Completed' AS  Col1
    	UNION 
    	SELECT'ZZZ-T13-PAB Chair Signature' AS  Col1 
    )
    
    SELECT Col1, CASE WHEN CHARINDEX('Signature', Col1) > 0 THEN
    	LTRIM(
    		RTRIM(
    			REVERSE(
    				SUBSTRING(
    					REVERSE(SUBSTRING(Col1, 1, CHARINDEX('Signature', Col1) - 1)), 
    					1, 
    					CHARINDEX('-', REVERSE(SUBSTRING(Col1, 1, CHARINDEX('Signature', Col1) - 1))) - 1
    				)
    			)
    		)
    	)
    	ELSE NULL
    	END AS Col2
    FROM CTE;


    A Fan of SSIS, SSRS and SSAS

    Friday, January 10, 2020 10:12 PM
  • Check this example too:

    declare @table as table ( Col1 varchar(100) )
    
    insert into @table values
    ( 'XXX-T09-CFO Signature' ),
    ( 'YYY-T140 - Update Funding Authorization Status to Completed' ),
    ( 'ZZZ-T13-PAB Chair Signature' ),
    ( 'Simple Signature' )
    
    
    select *,
    ( select left(value, len(value) - len(' Signature')) 
      from (
    	select TOP(1) * 
    	from ( 
    		select * 
    		from STRING_SPLIT(Col1, '-') 
    	) t 
    	where value LIKE '% Signature'
    ) t) as Col2
    from @table
    


    Saturday, January 11, 2020 9:31 AM
  • Hi CSKKingsForEver, 

    Please check following script.

    ;with cte as ( 
    SELECT 'XXX-T09-CFO Signature' AS  Col1  
    UNION all
    SELECT 'YYY-T140 - Update Funding Authorization Status to Completed'
    UNION all
    SELECT 'ZZZ-T13-PAB Chair Signature' 
    ),cte1 as (
    select Col1,case when charindex('Signature',Col1)=0 then null else 
    substring(Col1,1,charindex('Signature',Col1)-2) end Col2 
    from cte )
    select Col1,substring(Col2,len(Col2)-charindex('-',reverse(Col2))+2,charindex('-',reverse(Col2))-1) Col2
    from cte1
    /*
    Col1                                                        Col2
    ----------------------------------------------------------- -----------------------------------------------------------
    XXX-T09-CFO Signature                                       CFO
    YYY-T140 - Update Funding Authorization Status to Completed NULL
    ZZZ-T13-PAB Chair Siganature                                 PAB Chair
    */
    
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 13, 2020 7:17 AM
  •  

    Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 15, 2020 9:21 AM