Inserting space in a string based on the number of charcters in the string

Debilon 431 Reputation points

I have a column LegalDesignation inside OwnerNames tbl

i would like to find all the incidents where LegalDesignation is 4 characters long
where LEN(LegalDesignation) = 4

and then add a space after the left first two characters
LEFT(LegalDesignation,2)+' '+RIGHT(LegalDesignation,LEN(LegalDesignation)-LEN(LEFT(LegalDesignation,2)))

As you can see i can preform every step separately
but getting the syntax for the entire thing is a problem

Do i use case ? and if so how ?

Thank You

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

Accepted answer
  1. Yitzhak Khabinsky 25,206 Reputation points

    Hi @Debilon ,

    You need to learn how to ask questions as a minimal reproducible example.
    The answer below is following the same pattern. You copy it to SSMS as-is, run it, and it is working.

    A key to your question is use of the STUFF() function.


    -- DDL and sample data population, start  
    DECLARE @OwnerNames TABLE (ID INT IDENTITY PRIMARY KEY, LegalDesignation VARCHAR(100));  
    INSERT INTO @OwnerNames (LegalDesignation) VALUES  
    -- DDL and sample data population, end  
    SELECT *   
     , Result = IIF(LEN(LegalDesignation) = 4, STUFF(LegalDesignation, 3, 0, SPACE(1)), LegalDesignation)  
    FROM @OwnerNames;  


    | ID | LegalDesignation |   Result   |  
    |  1 | Debi             | De bi      |  
    |  2 | OwnerNames       | OwnerNames |  
    |  3 | 1234             | 12 34      |  
    0 comments No comments

0 additional answers

Sort by: Most helpful