SQL How to capture/check a particular text from standard column to populate value for calculated col-CodePudding

In SQL Server 2016, I have a table with a varchar column (title) that has some of the values as below

title
ProALPHA - S - HTML Custom Table implementation (E001445)
IKA CP Implementation (Aus) (E001534-0001)
Test Engagment Integration: (E001637-0003) Non-billable
Customer requests customization for Analytics and Java Migration - E000797
Create list with customers renewing in H2 2020

I want to populate the value for the calculated (derived) column based on the above title column in such a way that,

Find If the title column contains text E00 then only the derived column will have the E00 value else NULL

Example,

Thanks

CodePudding user response：

Assuming my question about that the value always suffixes the string, or the value you want is always suffixed by a right parenthesis, then you can do the following with some CHARINDEXs and SUBSTRING:

USE Sandbox;
GO

SELECT V.Title,
       SUBSTRING(V.Title,E.CI,RP.CI - E.CI) AS YourColumn
FROM (VALUES('ProALPHA - S - HTML Custom Table implementation (E001445)'),
            ('IKA CP Implementation (Aus) (E001534-0001)'),
            ('Test Engagment Integration: (E001637-0003) Non-billable'),
            ('Customer requests customization for Analytics and Java Migration - E000797'),
            ('Create list with customers renewing in H2 2020'))V(Title)
      CROSS APPLY (VALUES(NULLIF(CHARINDEX('E00',V.Title),0)))E(CI)
      CROSS APPLY (VALUES(ISNULL(NULLIF(CHARINDEX(')',V.Title,E.CI),0),LEN(V.Title) 1)))RP(CI);

db<>fiddle

CodePudding user response：

One option would be to use a combination of Charindex and Substring. Note in the following 100 should just be the declared length of the column - charindex will always stop at the end of the string.

This doesn't require ending parenthesis, it looks for the last digit.

with t as (
    select * from (values 
        ('ProALPHA - S - HTML Custom Table implementation (E001445)'                                    ),
        ('IKA CP Implementation (Aus) (E001534-0001)'                                                                   ),
        ('Test Engagment Integration: (E001637-0003) Non-billable'                                      ),
        ('Customer requests customization for Analytics and Java Migration - E000797'   ),
        ('Create list with customers renewing in H2 2020'                                                           )
    )t(title)
)
select title, 
   Iif(title like '