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

Question

Post reply

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

vikasjagadale8

Say Hey Kid

Points: 704
More actions
November 23, 2021 at 3:29 pm
Go to Answer
#3956126
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.
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
```

ScottPletcher

SSC Guru

Points: 101126
More actions
November 24, 2021 at 12:33 am
Answer
#3956277
```
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

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
                  ,')'
                  ,''
               )
);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Collins SSCrazy Points: 2174 More actions · Answer 2

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

vikasjagadale8 Say Hey Kid Points: 704 More actions · Answer 3

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 3 years, 1 month ago by vikasjagadale8. Reason: Updated the post with sample query and data