Click here to Skip to main content
14,599,188 members
Rate this:
Please Sign up or sign in to vote.
hi friends,

I am storing a value with comma into one table column I want to split by comma and that values need to be updated in another table.

First table columns:
EmpID casualLeaves  SickLeaves
  1        3             2


Second table columns:
EmpID  LeavesBalance
1       1 CL,1 SL


Now the first table need to update by the second table values output will be
EmpID casualLeaves  SickLeaves
  1        2            1
how to get the above output through sqlserver


What I have tried:

DECLARE @Leaves  varchar(50)

SELECT @Leaves = '1 CL,2 SL'

DECLARE @str varchar(50)

SET @str = 'SELECT ''' + REPLACE(@Leaves,',',''',''') + ''''
Exec @str
Posted
Updated 22-Apr-20 13:44pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

I really would advice you to change the structure of your tables. You try to store data inside a single column which should be stored as rows.

The proper design for the tables would be something like
Employee
- empid
- name...

leave
- empid
- leavetype (e.g. 1=casual, 2 = sick leave)
- leavedate

Now you can insert, delete and modify each leave separately and you don't need to store the amount of leaves in the employee table, you simply count them. For example
SELECT e.Name,
       (SELECT COUNT(*)
        FROM leave l
        WHERE l.empid = e.empid
        AND l.leavetype = 1) AS casual,
       (SELECT COUNT(*)
        FROM leave l
        WHERE l.empid = e.empid
        AND l.leavetype = 2) AS sick
FROM Employee e
   
Comments
prasanna204 18-Sep-16 21:04pm
   
okey Mika but I need to split 2nd table LeavesBalance column by comma and I want to difference them like this..3-1 for causual and 2-1 for sick leave the result is updated in the first table.
Rate this:
Please Sign up or sign in to vote.

Solution 2

Well... As Mika Wendelius[^] said, you have to re-think your database structure.

In the mean-while, have a look at this solution:
DECLARE @tab1 TABLE(EmpID INT, casualLeaves INT, SickLeaves INT)

INSERT INTO @tab1 (EmpID, casualLeaves , SickLeaves)
VALUES(1, 3, 2)

DECLARE @tab2 TABLE(EmpID INT, LeavesBalance VARCHAR(50))
INSERT INTO @tab2 (EmpID,  LeavesBalance)
VALUES(1, '1 CL,1 SL')


;WITH BalanceToLeaves AS
(
	--initial value
	SELECT EmpID, LEFT(LeavesBalance, CHARINDEX(',', LeavesBalance)-1) AS SingleLeave, RIGHT(LeavesBalance, LEN(LeavesBalance) -CHARINDEX(',', LeavesBalance)) AS Remainder
	FROM @tab2
	WHERE CHARINDEX(',', LeavesBalance)>0
	--recursive part
	UNION ALL
	SELECT EmpID, LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SingleLeave, RIGHT(Remainder, LEN(Remainder) -CHARINDEX(',', Remainder)) AS Remainder
	FROM BalanceToLeaves
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT EmpID, Remainder AS SingleLeave, NULL AS Remainder
	FROM BalanceToLeaves
	WHERE CHARINDEX(',', Remainder)=0
)
UPDATE C SET C.casualLeaves = C.casualLeaves + B.CasualLeave, 
	C.SickLeaves  = C.SickLeaves  + B.SickLeave 
FROM (
	SELECT EmpID, SUM(CasualLeave) AS CasualLeave, SUM(SickLeave) AS SickLeave
	FROM (
		SELECT EmpID, SingleLeave, CASE WHEN SingleLeave LIKE '%CL' THEN CONVERT(INT, LEFT(SingleLeave, CHARINDEX(' ', SingleLeave)-1)) END AS CasualLeave,
				CASE WHEN SingleLeave LIKE '%SL' THEN CONVERT(INT, LEFT(SingleLeave, CHARINDEX(' ', SingleLeave)-1)) END AS SickLeave
		FROM BalanceToLeaves) AS A
	GROUP BY EmpID ) AS B INNER JOIN @tab1 AS C ON B.EmpID = C.EmpID


After update @tabl contains:
EmpID	casualLeaves	SickLeaves
1		4				3


For further details, please see:
WITH common_table_expression (Transact-SQL)[^]
Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]
UPDATE from SELECT using SQL Server - Stack Overflow[^]
   
Comments
prasanna204 18-Sep-16 21:06pm
   
Thank you Maciej.I need to difference the values.
Maciej Los 19-Sep-16 1:49am
   
Well... Is there any problem to replace [+] with [-] in UPDATE statement?
Please, accept my answer as a solution (green button) - formally, to remove your question from unanswered list.
Cheers, Maciej
prasanna204 22-Sep-16 12:56pm
   
No problem Maciej.Thank you
Maciej Los 22-Sep-16 12:57pm
   
You're very welcome.
Rate this:
Please Sign up or sign in to vote.

Solution 3

Or you can keep it simple like this:


-- Here is the String Array you want to convert to a Table
declare @StringArray varchar(max)
set @StringArray = 'First item,Second item,Third item';

-- Here is the table which is going to contain the rows of each item in the String array
declare @@mytable table (EachItem varchar(50))

-- Just create a select statement appending UNION ALL to each one of the item in the array
set @StringArray = 'select ''' + replace(@StringArray, ',', ''' union all select ''') + ''''
-- Push the data into your table
insert into @@mytable exec (@StringArray)

-- You now have the data in an an array inside a table that you can join to other objects
select * from @@mytable
   
Comments
Richard Deeming 23-Apr-20 13:02pm
   
Extremely bad idea. If the value you're splitting is in any way controlled by the user, you've just opened yourself up to a SQL Injection[^] vulnerability.

If you're using SQL Server 2016 or later, use the STRING_SPLIT[^] function. Otherwise, use one of the many implementations available for earlier versions of SQL, none of which introduce a critical security vulnerability in the name of "simplicity".

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100