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

Jennis 21 Reputation points
2021-02-02T14:23:48.043+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,201 Reputation points
    2021-02-02T16:38:51.977+00:00

    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; 
    
    0 comments No comments

  2. Tom Phillips 17,716 Reputation points
    2021-02-02T16:49:45.637+00:00

    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
    
    0 comments No comments

  3. MelissaMa-MSFT 24,176 Reputation points
    2021-02-03T02:08:18.217+00:00

    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.

    0 comments No comments