Split string into array and get array in SQL server 2014

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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 18-Sep-16 6:59am

prasanna204

Updated 22-Apr-20 13:44pm

Maciej Los

v2

Add a Solution

3 solutions

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

SQL

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

Posted 18-Sep-16 7:10am

Wendelius

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.

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

Posted 22-Apr-20 13:45pm

tcsekhar

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".

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2016-09-18T12:21:00

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

In the mean-while, have a look at this solution:

SQL

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[^]