Splitting rows into multiple rows based on column values where delimitter is space

Question

I need to split the rows of table into multiple rows where delimitter is space,currently this is sql server 2012, so STRING_SPLIT is not working and compatibility is 110.In the given table task_No is auto incremented.

Answer 1

Hi @alfygraham ,

Welcome to the microsoft TSQL Q&A forum!

Since you did not provide the expected output, I did a test with my own data:

CREATE TABLE SourcetableB(ID INT,String VARCHAR(MAX))  
INSERT INTO SourcetableB VALUES(1,'abc def pqr xyz')  
,(2,'pqr xyz ghi abc')  
                             
--Create user-defined functions(applies to SQL Server all supported versions)   
CREATE FUNCTION SplitStr(@Sourcestr VARCHAR(8000), @Seprate VARCHAR(100))       
RETURNS @result TABLE(F1 VARCHAR(100))       
  AS         
   BEGIN       
   DECLARE @sql AS VARCHAR(100)       
  SET @Sourcestr=@Sourcestr+@Seprate         
  WHILE(@Sourcestr<>'')       
  BEGIN       
    SET @sql=left(@Sourcestr,CHARINDEX(' ',@Sourcestr,1)-1)       
    INSERT @result VALUES(@sql)       
     SET @Sourcestr=STUFF(@Sourcestr,1,CHARINDEX(' ',@Sourcestr,1),'')       
   END       
   RETURN    
   END  
GO  
  
SELECT *   
FROM SourcetableB s   
CROSS APPLY SplitStr(S.string,' ') V;  
  
DROP FUNCTION SplitStr  
DROP TABLE SourcetableB  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

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.

Answer 2

This short article on my web site includes a string-splitter for low-level versions, and has examples how split columns in tables: https://www.sommarskog.se/arrays-in-sql.html.

You really need to review the design of that table. For each column should ask yourself what it means that a column is NULL. If you don't know the answer, the column should be NOT NULL. (For instance, what does a row with an ID and all NULL mean?)

You also need to review the data types. Columns by the name of Start_Date and Close_Date should probably be of the data type date. using string for date and time will cause you grief. Also, bear in mind that MAX columns comes with overhead, so don't use it when you don't need it.