How to capture particular text from standard column to populate computed column

  • In SQL Server 2016, While Creating a table I want to populate a computed column that will get a value of E00... or NULL based on the value of the standard title column.

    So, If the title column contains text E00.. then only the computed column will have the E00.. value else NULL.

    As shown in the below-attached Example, text E00.. in the title column could be at the beginning, in-between, or at the end. It may or may not have brackets.Expected_Res

    So far with the below code, I can get the expected output, except for Row 7 & 8 of the attached example.

    Can someone please help to modify the existing code or provide a different approach to get expected results for all rows?

    https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=91fb705a45925e092082f9f6681480c8

    create function dbo.E00_Part(@title varchar(100))returns varchar(100)asbeginreturn (select Reverse(Substring(@title, CharIndex('E00',@title), 100 )))end

    create table T (title varchar(100), Computed as Iif(title like '%E00%',Reverse(Substring(dbo.E00_Part(title),patindex('%[0-9]%',dbo.E00_Part(title)),100)),null))

    insert into T 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' ),('Bank Payments Testing Solution (E000498) | 01 Discover'),('E000710 Clarify why backlog is 0')

    select * from t

     

  • Try this:

    CREATE TABLE T
    (
    title VARCHAR(100) NOT NULL
    ,Computed AS
    REPLACE(
    CASE CHARINDEX('E00', title)
    WHEN 0 THEN
    NULL
    ELSE
    SUBSTRING(
    title
    ,CHARINDEX('E00', title)
    ,CHARINDEX(' ', title + ' ', CHARINDEX('E00', title)) - CHARINDEX('E00', title)
    )
    END
    ,')'
    ,''
    )
    );

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • From a design perspective why does the table need to know all the information about how characters are extracted from another column?  It seems like whatever is inserting the row should get it right and not need extra extraction logic.  Maybe a constraint on 'computed' (or whatever it's more appropriately called) which requires (CHARINDEX(Computed Column, title)>0 or NULL) would suffice?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Phil Parkin wrote:

    Try this:

    CREATE TABLE T
    (
    title VARCHAR(100) NOT NULL
    ,Computed AS
    REPLACE(
    CASE CHARINDEX('E00', title)
    WHEN 0 THEN
    NULL
    ELSE
    SUBSTRING(
    title
    ,CHARINDEX('E00', title)
    ,CHARINDEX(' ', title + ' ', CHARINDEX('E00', title)) - CHARINDEX('E00', title)
    )
    END
    ,')'
    ,''
    )
    );

    Thank you, @Phil Parkin, but there are a few more scenarios, wherein I am not getting the expected output in the computed column. Can you please help here?

    title

    1. E000060: Set end date to 30.5.2021

    2. First Corporation (UK) - HQ-Fiserv ED CO3 (E000586-0004)_Dan S

    3. CA DMV - Performance testing (E001504)(Load)

    4. D-MS-GIP: Testmanagement & Test Data Management (& automation) (E000123),  billable EM resource, remote, EMEA

    5. Twinformatics U - S - Consulting Roll out (E000089)>, <EM Resource, <remote>, EMEA

    https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=8eaef0a2b7b468b907e0e80ed8fff18b

     

    • This reply was modified 8 months, 4 weeks ago by  vikasjagadale8. Reason: Updated the post with sample query and data
  •  

    SELECT title, 
    SUBSTRING(title, CHARINDEX('E00', title), 200),
    Computed = CASE WHEN CHARINDEX('E00', title) = 0 THEN NULL
    ELSE SUBSTRING(title, CHARINDEX('E00', title), PATINDEX('%[^0-9-]%',
    SUBSTRING(title, CHARINDEX('E00', title) + 1, 200) + '.')) END
    FROM dbo.t

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply