SQL Query Help - Split into 3 columns

Question

Hi:

Need help with SQL query. I have this output from the Account column splitted into 3 seperate columns, but the period in that is not consistent.

CREATE TABLE #tblTest  
(UID int,  
 Account varchar(50))  
  
 INSERT INTO #tblTest VALUES (101, '2021.20.EARNED')  
 INSERT INTO #tblTest VALUES (102, '2020.20.EARNED')  
 INSERT INTO #tblTest VALUES (103, '35.CASHED')  
 INSERT INTO #tblTest VALUES (104, '2019.25.CASHED')  
 INSERT INTO #tblTest VALUES (105, '15.ECONOMY.CASH')  
  
 SELECT * FROM #tblTest  

EXPECTED OUTPUT:

Answer 1

Hi @SQL ,

Please try the following solution.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (UID int,  Account varchar(50));  
INSERT INTO @tbl VALUES   
(101, '2021.20.EARNED'),  
(102, '2020.20.EARNED'),  
(103, '35.CASHED'),  
(104, '2019.25.CASHED'),  
(105, '15.ECONOMY.CASH');  
-- DDL and sample data population, end  
  
SELECT *   
 , PARSENAME(Account, 3) AS col1  
 , PARSENAME(Account, 2) AS col2  
 , PARSENAME(Account, 1) AS col3  
FROM @tbl;  

Output

+-----+-----------------+------+---------+--------+  
| UID |     Account     | col1 |  col2   |  col3  |  
+-----+-----------------+------+---------+--------+  
| 101 | 2021.20.EARNED  | 2021 | 20      | EARNED |  
| 102 | 2020.20.EARNED  | 2020 | 20      | EARNED |  
| 103 | 35.CASHED       | NULL | 35      | CASHED |  
| 104 | 2019.25.CASHED  | 2019 | 25      | CASHED |  
| 105 | 15.ECONOMY.CASH | 15   | ECONOMY | CASH   |  
+-----+-----------------+------+---------+--------+  

Answer 2

It's your lucky day!

That is, SQL Server has a built-in function that meets your need exactly:

   SELECT UID, Account, parsename(Account, 3), parsename(Account, 2), parsename(Account, 1)  
         FROM #tblTest  

The parsename function intended to be used to parse object names in SQL Server, and there could be some funny effects if there are brackets or double quotes in these strings.