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

Debilon 431 Reputation points
2022-03-17T12:50:03.52+00:00

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

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

Accepted answer
  1. Yitzhak Khabinsky 26,201 Reputation points
    2022-03-17T13:04:23.367+00:00

    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.

    T-SQL

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

    Output

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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.