How to eliminates if the column value in a table starts with some word

Question

Good Morning,

if the Description column value starts with STATES then remove it in output if STATES_ appears then need to remove from output.

just checking is there any way other than replace(replace(Description, 'STATES',''),'STATES_','') .. please help me.

Create Table #mytemp (Id varchar(10), Description varchar(30))

INSERT INTO #mytemp Values ('X01','STATES CHAMBER')
INSERT INTO #mytemp Values ('X01','LANCASTER Fords')
INSERT INTO #mytemp Values ('X01','MEMBER SYNERGY')
INSERT INTO #mytemp Values ('X01','STATES_LOC')
INSERT INTO #mytemp Values ('X01','STATES MOCHAL')
INSERT INTO #mytemp Values ('X01','STATE GOVT')

Thank you
Asiti

Answer 1

if the Description column value starts with STATES then remove it in output if STATES_ appears then need to remove from output.

IMHO, the REPLACE() function is not suitable for the scenario. It will replace a string found anywhere not necessarily at the beginning of the string.

Please try the following approach.

SQL

-- DDL and sample data population, start
DECLARE @tbl Table (Id varchar(10), Description varchar(30))
INSERT INTO @tbl Values 
('X01','STATES CHAMBER'),
('X01','LANCASTER Fords'),
('X01','MEMBER SYNERGY'),
('X01','STATES_LOC'),
('X01','STATES MOCHAL'),
('X01','STATE GOVT');
-- DDL and sample data population, end

DECLARE @remove VARCHAR(20) = 'STATES_';

SELECT id, Description AS [Before]
    , IIF(LEFT(Description, LEN(@remove)) = @remove, STUFF(Description, 1, LEN(@remove), ''), Description) AS [After]
FROM @tbl; 

Answer 2

Since you are looking at starting with STATES, this should work:

DROP TABLE IF EXISTS #mytemp;

Create Table #mytemp (Id varchar(10), Description varchar(30))

INSERT INTO #mytemp Values ('X01','STATES CHAMBER')
INSERT INTO #mytemp Values ('X01','LANCASTER Fords')
INSERT INTO #mytemp Values ('X01','MEMBER SYNERGY')
INSERT INTO #mytemp Values ('X01','STATES_LOC')
INSERT INTO #mytemp Values ('X01','STATES MOCHAL')
INSERT INTO #mytemp Values ('X01','STATE GOVT')
INSERT INTO #mytemp Values ('X01','STATES*AAAA')
INSERT INTO #mytemp Values ('X01','STATES-Test')


UPDATE t
    SET [Description] = REPLACE([Description],replacethese.ToBeReplaced,'')
FROM #mytemp t
    INNER JOIN (
        SELECT DISTINCT LEFT([Description],7) as ToBeReplaced
        FROM #mytemp
        WHERE [Description] LIKE 'STATES[^A-Za-z0-9]%'
    ) replacethese
    ON replacethese.ToBeReplaced = LEFT([Description],7)

SELECT *
FROM #mytemp

Answer 3

Hi @aswaniD-8992,

Welcome to Microsoft Q&A!

Please also refer below and check whether it is helpful to you.

Select

 Select case when Description like 'STATES[^A-Za-z0-9]%'  
 then replace(replace(Description,substring(Description,patindex('%[^!A-Za-z0-9]%',Description),1),''),'STATES','')  
 else Description end Description  
 from #mytemp  

Update

 update #mytemp  
 set Description=case when Description like 'STATES[^A-Za-z0-9]%'  
 then replace(replace(Description,substring(Description,patindex('%[^!A-Za-z0-9]%',Description),1),''),'STATES','')  
 else Description end  
  
 select * from #mytemp  

Output:

Id Description  
X01 CHAMBER  
X01 LANCASTER Fords  
X01 MEMBER SYNERGY  
X01 LOC  
X01 MOCHAL  
X01 STATE GOVT  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.