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?
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