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

Question

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

Answer 1

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