May 22, 2012 at 12:10 am
I have a table in which there are comma delimited fields, I want to update them based on conditions
if my additional_procedure location field contains null or not 1 and 2 i need to make it 1
I am using following method
1) spiliting into rows
2) checking those values where ever there is null, not 1 and not 2, set it to 1
3) again making those rows into comma delimited field
4) I want to put that new comma delimited field into table
here is temp table
drop Table #temp
Create Table #temp
(MRN nchar(10) null,
additional_procedure varchar(max),
additional_procedure_Location varchar(max))
insert into #temp
(MRN,additional_procedure,additional_procedure_Location)
values('1','9772800,9773100,9773200','1,2,3'),
('2','9773300, 9773400, 9773500, 9773600','2,1,,1'),
('3','9774300,9774400,9774500,9774600','1,2,2,4'),
('4','9775300,9775400,9776100','4,,2'),
('5','9776500,9776800,9776900,9777100','2,1,,1')
Select * from #temp
I wrote 2 table valued functions first one to spilit them into rows and than another to find out if there is null or not 1 and not 2 and for setting value to 1
I also made a procedure in which I am passing two parameters and getting the new comnplete field in comma delimited format , however I am unable toput this new comma delimited field into the table and need help how to update it, your help would be highly appreciated
my functions are as below
First Function to spilit these comma delimited fields into rows
GO
/****** Object: UserDefinedFunction [dbo].[Validate_2_DelimitedFields_Id] Script Date: 05/22/2012 15:27:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Validate_2_DelimitedFields_Id]
(
@delimited1 nvarchar(max),
@delimited2 nvarchar(max),
@delimiter1 nvarchar(100),
@delimiter2 nvarchar(100),
@StartFieldNum int,
@EndFieldNum int
) RETURNS @t TABLE
(
id int,
val1 nvarchar(max),
val2 nvarchar(max)
)
AS
BEGIN
insert into @t(id,val1,val2)
select a.id,a.val,b.val from [HealthValidator].[dbo].[ValidateDelimitedField] (@delimited1,',',1,50) as a full outer join
[HealthValidator].[dbo].[ValidateDelimitedField] (@delimited2,',',1,50) as b on a.Id = b.Id
RETURN
END
GO
Second funtion to find where is not 1 not 2 and null values and setting it to 1
CREATE FUNCTION [dbo].[UpdateProcedureLocation]
(
-- Add the parameters for the function here
@ap varchar(max),
@apl varchar(max)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] int NULL,
[ap] [varchar](max) NULL,
[apl] [varchar](max) NULL
)
AS
BEGIN
INSERT INTO @ReturnTable(id,ap,apl)
Select * from dbo.Validate_2_DelimitedFields_id(@ap, @apl ,',',',',1,26)
Update @ReturnTable
Set apl = '1'
where Id in (Select id from dbo.Validate_2_DelimitedFields_id(@ap, @apl ,',',',',1,26)
where ((nullif(val1,'') is not null ) and nullif(val2,'') is null) or
(nullif(val1,'') is not null ) and (val2 <> '2' and val2 <> '1'))
RETURN
END
and third procedure in which I am passing these two and getting a new field in the same comma delimited format
create proc [dbo].[Sp_UpdateProcedureLocation]
@ap varchar(Max),
@apl varchar(Max),
@newapl varchar(Max) output
as
declare
@TEMPTable TABLE (id int, ap varchar(20), apl varchar(20))
Insert into @TEMPTable(id,ap,apl)
Select * from dbo.[UpdateProcedureLocation](@ap, @apl)
DECLARE @NewProcLoc VARCHAR(MAX)
SELECT @NewProcLoc = COALESCE(@NewProcLoc+',' ,'') + apl
FROM @temptable
SELECT @NewProcLoc
Set @newapl = @NewProcLoc
GO
My required output is
drop Table #temp
Create Table #temp
(MRN nchar(10) null,
additional_procedure varchar(max),
additional_procedure_Location varchar(max))
insert into #temp
(MRN,additional_procedure,additional_procedure_Location)
values('1','9772800,9773100,9773200','1,2,1'),
('2','9773300, 9773400, 9773500, 9773600','2,1,1,1'),
('3','9774300,9774400,9774500,9774600','1,2,2,1'),
('4','9775300,9775400,9776100','1,1,2'),
('5','9776500,9776800,9776900,9777100','2,1,1,1')
Select * from #temp
May 22, 2012 at 7:57 am
What you have is the beginning of your worst nightmare. The challenges you are facing is the reason you shouldn't store more than 1 value in a single column. You should normalize your data so you don't have to do this.
The code you posted is really not much of anything to go on. Everything has more functions called and we have no ddl for those.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2012 at 8:10 am
+1 - see the following on how to get your data into 1st normal form
http://en.wikipedia.org/wiki/First_normal_form
MVDBA
May 22, 2012 at 8:20 am
I agree with the previous statements made, you are going to have many difficulties.
However, your current design can be used if it absolutely has to.
SELECT MRN, additional_procedure, additional_procedure_Location,
STUFF((SELECT ','+
CASE WHEN Item NOT IN ('1','2') OR Item = ''
THEN '1'
ELSE Item END AS Item
FROM #temp data
CROSS APPLY dbo.DelimitedSplit8k(data.additional_procedure_Location,',') split
WHERE tbl.MRN = data.MRN AND tbl.additional_procedure = data.additional_procedure
AND tbl.additional_procedure_Location = data.additional_procedure_Location
ORDER BY ItemNumber
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS new_additional_procedure_Location
FROM #temp tbl;
Which returns: -
MRN additional_procedure additional_procedure_Location new_additional_procedure_Location
---------- --------------------------------------- ------------------------------ ------------------------------------
1 9772800,9773100,9773200 1,2,3 1,2,1
2 9773300, 9773400, 9773500, 9773600 2,1,,1 2,1,1,1
3 9774300,9774400,9774500,9774600 1,2,2,4 1,2,2,1
4 9775300,9775400,9776100 4,,2 1,1,2
5 9776500,9776800,9776900,9777100 2,1,,1 2,1,1,1
This solution uses the 8K splitter by Jeff Moden to actually split your list (code below). It will only work if your "additional_procedure_Location" is never larger than 8000 characters.
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
May 22, 2012 at 9:54 am
I'm sorry you have to deal with this crap. It is the result of a VB programmer gone wild with a database to play with. Sorta like giving a machine gun to a 14 year old.
The probability of survival is inversely proportional to the angle of arrival.
May 22, 2012 at 10:42 am
You should probably look at restructuring this table into two or more tables (I haven't really looked at). If the application must have the data presented to it as it is currently structured, you can hide the changes to the table behind a view. The view would have to be given the same name as the current table.
You can either tackle this project yourself or consider hiring a contractor to do the work for you. Which way you go depends on what your organization thinks about the changes that would need to be done, and if it is critical to complete.
I am sure that we could answer questions, but I am not sure if this is something that could be done via the forums. One reason is that we are all volunteers here and the time it would take to assist you could be considerable when looking at our other commitments.
May 22, 2012 at 11:36 am
umar.memon (5/22/2012)
I have a table in which there are comma delimited fields...
You don't say whether this design is yours or not, but if you have the option of changing it, you should. If you are new to terms like 'first normal form', you might like to read my introduction here: http://www.sqlservercentral.com/articles/Database+Design/72054/.
May 22, 2012 at 5:09 pm
Hi guys, It was our need to keep data in comma delimited field, how ever
I wrote following query to get those back in comma delimited field, I have done this after posting as I was keep trying.
sometimes we need to do what we don't want to 🙂 so it was challenging to do however it was out requirment
Now I need to update the table by taking thse values so i will do it and will post a article on it. why I needed and how I have done it
select
t.MRN,
stuff((
select ',' + t1.[apl]
from (Select * from dbo.proceduretemp
CROSS APPLY
dbo.[UpdateProcedureLocation](additional_procedure, additional_procedure_location)
) as t1
where t.MRN = t1.MRN
for xml path('')
),1,1,'') as name_csv
from (Select * from dbo.proceduretemp
CROSS APPLY
dbo.[UpdateProcedureLocation](additional_procedure, additional_procedure_location)
) as t
group by t.MRN
May 22, 2012 at 5:14 pm
Cadavre (5/22/2012)
I agree with the previous statements made, you are going to have many difficulties.However, your current design can be used if it absolutely has to.
SELECT MRN, additional_procedure, additional_procedure_Location,STUFF((SELECT ','+
CASE WHEN Item NOT IN ('1','2') OR Item = ''
THEN '1'
ELSE Item END AS Item
FROM #temp data
CROSS APPLY dbo.DelimitedSplit8k(data.additional_procedure_Location,',') split
WHERE tbl.MRN = data.MRN AND tbl.additional_procedure = data.additional_procedure
AND tbl.additional_procedure_Location = data.additional_procedure_Location
ORDER BY ItemNumber
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS new_additional_procedure_Location
FROM #temp tbl;
Which returns: -
MRN additional_procedure additional_procedure_Location new_additional_procedure_Location---------- --------------------------------------- ------------------------------ ------------------------------------
1 9772800,9773100,9773200 1,2,3 1,2,1
2 9773300, 9773400, 9773500, 9773600 2,1,,1 2,1,1,1
3 9774300,9774400,9774500,9774600 1,2,2,4 1,2,2,1
4 9775300,9775400,9776100 4,,2 1,1,2
5 9776500,9776800,9776900,9777100 2,1,,1 2,1,1,1
This solution uses the 8K splitter by Jeff Moden to actually split your list (code below). It will only work if your "additional_procedure_Location" is never larger than 8000 characters.
CREATE FUNCTION dbo.DelimitedSplit8K--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
Thanks Cadavre, I have done it, so nice of you for your answer and I saw your answer after I wrote this query
select
t.MRN,
stuff((
select ',' + t1.[apl]
from (Select * from dbo.proceduretemp
CROSS APPLY
dbo.[UpdateProcedureLocation](additional_procedure, additional_procedure_location)
) as t1
where t.MRN = t1.MRN
for xml path('')
),1,1,'') as name_csv
from (Select * from dbo.proceduretemp
CROSS APPLY
dbo.[UpdateProcedureLocation](additional_procedure, additional_procedure_location)
) as t
group by t.MRN
it is similar to your's however your one is efficent and I will make mine as similar like yours
May 22, 2012 at 5:41 pm
I am wondering Can we make any table valued function to do this ?
where I can make it generic and which can be used with any other table
hi guys,
CREATE FUNCTION [dbo].[UpdatedProcedureLocation]
(
@Additional_Procedure as varchar(max),
@Additional_Procedure_Location as varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @Ret_New_additional_Procedure as varchar(max)
SELECT @Ret_New_additional_Procedure =STUFF((SELECT ','+
CASE WHEN (val2 NOT IN ('1','2') OR val2 is null)
THEN '1'
ELSE val2 END AS val2
FROM dbo.Validate_2_DelimitedFields_id(@Additional_Procedure, @Additional_Procedure_Location ,',',',',1,26) split
ORDER BY id
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')
-- Return the result of the function
RETURN @Ret_New_additional_Procedure
END
here is a generic scalar valued function which will give us a newvalue and than I used and table trigger to update the value 😉
May 22, 2012 at 7:31 pm
For a demonstration in awkwardness and to avoid the initial split string, I humbly offer the following solution:
Create Table #temp
(MRN nchar(10) null,
additional_procedure varchar(max),
additional_procedure_Location varchar(max))
insert into #temp (MRN,additional_procedure,additional_procedure_Location)
SELECT '1','9772800,9773100,9773200','1,2,3'
UNION ALL SELECT '2','9773300, 9773400, 9773500, 9773600','2,1,,1'
UNION ALL SELECT '3','9774300,9774400,9774500,9774600','1,2,2,4'
UNION ALL SELECT '4','9775300,9775400,9776100','4,,2'
UNION ALL SELECT '5','9776500,9776800,9776900,9777100','2,1,,1'
;WITH Tally (n) AS (
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),
Parser AS (
SELECT MRN, additional_procedure, x, r
FROM #temp
CROSS APPLY (
SELECT CASE WHEN n = 1 AND SUBSTRING(additional_procedure_Location, n, 1) = ',' THEN '1'
WHEN n = 1 AND SUBSTRING(additional_procedure_Location, n, 1) NOT IN ('1','2',',') THEN '1'
WHEN n = 1 THEN SUBSTRING(additional_procedure_Location, n, 1)
WHEN n <> LEN(additional_procedure_Location) AND
SUBSTRING(additional_procedure_Location, n, 1) <> ',' AND
SUBSTRING(additional_procedure_Location, n-1, 1) <> ',' THEN ''
WHEN n <> LEN(additional_procedure_Location) AND
SUBSTRING(additional_procedure_Location, n, 1) = ',' AND
SUBSTRING(additional_procedure_Location, n-1, 1) = ',' THEN '1,'
WHEN n <> LEN(additional_procedure_Location) AND
SUBSTRING(additional_procedure_Location, n, 1) NOT IN ('1','2',',') THEN '1'
WHEN n <> LEN(additional_procedure_Location) THEN SUBSTRING(additional_procedure_Location, n, 1)
WHEN n = LEN(additional_procedure_Location) AND
SUBSTRING(additional_procedure_Location, n, 1) <> ',' AND
SUBSTRING(additional_procedure_Location, n-1, 1) <> ',' THEN ''
WHEN n = LEN(additional_procedure_Location) AND
SUBSTRING(additional_procedure_Location, n, 1) = ',' AND
SUBSTRING(additional_procedure_Location, n-1, 1) = ',' THEN '1,'
WHEN n = LEN(additional_procedure_Location) AND
SUBSTRING(additional_procedure_Location, n, 1) NOT IN ('1','2',',') THEN '1'
WHEN n = LEN(additional_procedure_Location) AND
SUBSTRING(additional_procedure_Location, n, 1) = ',' THEN ',1'
ELSE SUBSTRING(additional_procedure_Location, n, 1) END
,ROW_NUMBER() OVER (PARTITION BY MRN ORDER BY (SELECT NULL))
FROM Tally
WHERE n BETWEEN 1 and LEN(additional_procedure_Location))x(x, r)),
PutItTogether AS (
SELECT MRN, additional_procedure,
(SELECT x
FROM Parser p2
WHERE p1.MRN = p2.MRN
FOR XML PATH(''), root('M'), type).value('/M[1]','varchar(max)' ) As additional_procedure_Location
FROM Parser p1)
SELECT MRN, MAX(additional_procedure), MAX(additional_procedure_Location)
FROM PutItTogether
GROUP BY MRN
DROP TABLE #temp
You may need to adjust the TOP 1000 on the Tally table to handle the maximum length of your VARCHAR(MAX), which I am too lazy to look up.
I figure, if you're gonna live with bad database design, you can probably live with bad SQL queries as well.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 22, 2012 at 11:29 pm
I am looking for a Table Valued function to do this functionality, pass comma delimited field, and than it will treturn me an updated one so I can use that in a other places as well
May 22, 2012 at 11:38 pm
umar.memon (5/22/2012)
I am looking for a Table Valued function to do this functionality, pass comma delimited field, and than it will treturn me an updated one so I can use that in a other places as well
Why a TVF? I would think that a Scalar Valued Function that you apply to each row of your table (like a SplitString) would be better?
My view is that it would be a good learning experience for you to apply the excellent examples (excluding mine) provided in this thread to construct this on your own.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 23, 2012 at 12:19 am
Not sure if this will work, but here is a shot:
CREATE FUNCTION dbo.itvfn_UpdatedProcedureLocation
(
@Additional_Procedure as varchar(max),
@Additional_Procedure_Location as varchar(max)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN (
SELECT STUFF((SELECT ',' +
CASE WHEN (val2 NOT IN ('1','2') OR val2 is null)
THEN '1'
ELSE val2 END AS val2
FROM dbo.Validate_2_DelimitedFields_id(@Additional_Procedure, @Additional_Procedure_Location ,',',',',1,26) split
ORDER BY id
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') UpdateProcLocation
)
May 23, 2012 at 12:41 am
Lynn Pettis (5/23/2012)
Not sure if this will work, but here is a shot:
CREATE FUNCTION dbo.itvfn_UpdatedProcedureLocation
(
@Additional_Procedure as varchar(max),
@Additional_Procedure_Location as varchar(max)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN (
SELECT STUFF((SELECT ',' +
CASE WHEN (val2 NOT IN ('1','2') OR val2 is null)
THEN '1'
ELSE val2 END AS val2
FROM dbo.Validate_2_DelimitedFields_id(@Additional_Procedure, @Additional_Procedure_Location ,',',',',1,26) split
ORDER BY id
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') UpdateProcLocation
)
I've seen (dramatically) how WITH SCHEMABINDING improves the performance of a Scalar Valued Function.
I'm wondering if the same is true for TVFs and what is the downside?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply