Author |
Topic |
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2013-04-19 : 07:39:57
|
I have 2 parameters in my Stored Procdure. I want to find the values not in 2nd parameterstr_emp_chennai = 'Raj,Kumar,Selvam'str_emp_delhi = 'Kannan,Raj,Gopal'I need result to bestr_new_emp = 'Kumar,Selvam'The employee and the name as for represntaion. my scneario have other values. kinldy help me with logic for spliting and comparing string based on ","THANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 08:05:10
|
Create one UDF to split CSV and then run the following query-- UDF: CustomSplitCREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(4000))RETURNS tableASRETURN (WITH Pieces(n, start, stop) AS (SELECT 1, 1, CHARINDEX(@sep, @s)UNION ALLSELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)FROM PiecesWHERE stop > 0)SELECT n,SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS ValFROM Pieces)GO-- Query to get required outputDECLARE @str_emp_chennai VARCHAR(1000)= 'Raj,Kumar,Selvam'DECLARE @str_emp_delhi VARCHAR(1000) = 'Kannan,Raj,Gopal'SELECT STUFF((SELECT ','+ Val FROM (SELECT Val FROM CustomSplit(',', @str_emp_chennai) EXCEPT SELECT Val FROM CustomSplit(',', @str_emp_delhi) ) t FOR XML PATH ('') ), 1, 1, '') --Chandu |
|
|
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2013-04-19 : 08:12:32
|
can you give me in stored procedureTHANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 08:19:12
|
whats the problem with UDF (user-Defines Function) for Splitting?--Chandu |
|
|
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2013-04-19 : 08:36:04
|
Since we dont have any function in this project till now , i would like to have in SP if possibleTHANKSSHANMUGARAJnshanmugaraj@gmail.com |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 09:09:08
|
[code]GOCREATE PROCEDURE Split_CompareProc(@Param1 VARCHAR(2000),@Param2 VARCHAR(2000),@Result VARCHAR(2000) OUTPUT)ASBEGIN SELECT @Result = ISNULL(@Result, '') + ',' + wrd FROM (SELECT p.q.value('.', 'varchar(100)') as wrd FROM (SELECT CAST('<Values><Value>' + REPLACE( @Param1 , ',', '</Value><Value>') + '</Value></Values>' AS xml) AS m)t CROSS APPLY m.nodes('/Values/Value')p(q) EXCEPT SELECT p.q.value('.', 'varchar(100)') as wrd FROM (SELECT CAST('<Values><Value>' + REPLACE( @Param2 , ',', '</Value><Value>') + '</Value></Values>' AS xml) AS m)t CROSS APPLY m.nodes('/Values/Value')p(q) ) tmp SELECT STUFF(@result, 1, 1, '')ENDGODECLARE @str_emp_chennai VARCHAR(1000)= 'Raj,Kumar,Selvam', @result VARCHAR(2000)DECLARE @str_emp_delhi VARCHAR(1000) = 'Kannan,Raj,Gopal'EXEC Split_CompareProc @str_emp_chennai, @str_emp_delhi, @result OUT/*Output: Kumar,Selvam */ [/code]--Chandu |
|
|
|
|
|