CODEWITHSUNDEEP
sql-serverrecursion
user2726975
user2726975

Reputation: 1353

Recursive clean up column in SQL server

I have a table that needs clean up based on search criteria.

Create table dbo.tempNames(ID int null, Name varchar(500) null)
INSERT INTO dbo.tempNames values(1,'ABC Financial')
INSERT INTO dbo.tempNames values(1,'ABC Financial Company')
INSERT INTO dbo.tempNames values(1,'ABC Financial Company solely in its capacity')
INSERT INTO dbo.tempNames values(1,'ABC Financial in its capacity')

I have a rule that says remove everything after particular substring 1) solely 2) in its capacity

HEre's what I wrote

SELECT ID, CASE WHEN Name LIKE '%in its capacity%' THEN SUBSTRING(Name,1,charindex('In Its Capacity',Name,1)-1)
                WHEN Name LIKE '%solely%' THEN  SUBSTRING(Name,1,charindex('solely',Name,1)-1)
                ELSE Name END As ChangedName
                FROM dbo.tempNames

For row#3, ChangedName becomes 'ABC Financial Company solely'. How can I recursively do this until all the strings are updated.

Expected output

Create table dbo.outputNames(ID int null, ChangedName varchar(500) null)
INSERT INTO dbo.outputNames values(1,'ABC Financial')
INSERT INTO dbo.outputNames values(1,'ABC Financial Company')
INSERT INTO dbo.outputNames values(1,'ABC Financial Company')
INSERT INTO dbo.outputNames values(1,'ABC Financial')

Thanks

Upvotes: 0

Views: 53

Answers (1)

Nolan Shang
Nolan Shang

Reputation: 2328

It the another sample, searching all the keywords and get the first place.

SELECT ID,a.pos,LEFT(Name,ISNULL(a.pos,LEN(name))) AS changename
FROM dbo.tempNames
CROSS APPLY( SELECT MIN(NULLIF(c.pos,0)) AS pos FROM 
 (VALUES(CHARINDEX(' solely',Name)),(CHARINDEX(' in its capacity',Name))) c(pos)
) a

ID          pos         changename
----------- ----------- --------------
1           NULL        ABC Financial
1           NULL        ABC Financial Company
1           22          ABC Financial Company 
1           14          ABC Financial

Upvotes: 2

Related Questions