asked
update comma delimited field with data from another table
Hi All, I am trying to update a field with data from another table. Normally this is a no brainer however this time I have a comma delimited field that contains the primary key(s) to data in another table. On top of that the data field isn't always clean. For example the field I'm trying to update can look like any of the following: -1- 78ebba0bbd2bd440329584a245630d06 -2- Physical Access Control / Building Security -3- b9000d8bbd571000329584a245630d39,9e000d8bbd571000329584a245630d4a -4- 83000d8bbd571000329584a245630d90,Patient Admitting and Registration and the kicker which is only 2 pieces of information not 3 -5- 8e429b0bbd6fd440329584a245630dc6,Scheduling, Scripting and Automation Tools There are several updates occuring in the parent procedure, and so far I've tried several things (function, stored procedure, dynamic sql) and always keep coming back a stalemate when attempting to update this field (I actually have 3 fields with this same issue). I did create a proc that does seem to work, but only for a single record at a time so I'm sure there has to be a better way - I don't want to use a cursor. Below is the procedure I currently run to return "names" - as long as there exist match(s) for the field data. Any thoughts on how I can get this to work are greatly appreciated. Thanks. DECLARE @AppID VARCHAR(25) SET @AppID = 'APP006670' DECLARE @SQL VARCHAR(2000) DECLARE @MApptypeID VARCHAR(500) DECLARE @Apptype VARCHAR(5000) SET @MApptypeID = ( SELECT '''' + replace( CASE WHEN isnull(cast(atn."u_name" AS VARCHAR (500)),'none') = 'none' THEN cast("u_application_type" AS VARCHAR(500)) ELSE cast(atn."u_name" AS VARCHAR(500)) END,',',''',''') + '''' AS "u_application_type" FROM dbo.Application app left outer join dbo.Apptype atn ON cast(app.u_application_type AS VARCHAR(500))=cast(atn.sys_id AS VARCHAR(500)) WHERE cast("u_number" AS VARCHAR(50)) = @AppID ) PRINT @MApptypeID TRUNCATE TABLE dbo.MApptype SET @SQL = 'INSERT INTO dbo.MApptype SELECT u_name FROM [dbo].[Apptype] WHERE [sys_id] IN (' + @MApptypeID + ') UNION ALL SELECT u_name FROM [dbo].[Apptype] WHERE [u_name] IN (' + @MApptypeID + ')' EXECUTE (@SQL) SELECT @Apptype = coalesce(@Apptype + ',', '') + u_name FROM dbo.MApptype PRINT @Apptype
So, wait. Is it at most two pieces of information? And hex-type strings are either first (and only), or first (followed by something else), or first and second? ie, the input can't contain something like wibble,wobble,woo, 83000d8bbd571000329584a245630d90
Problem Solved! This 2 function approach does exactly what I need it to do. --This is function 1 CREATE FUNCTION [dbo].[fParsing_Table] ( @list NVARCHAR(1024) , @delimiter NCHAR(1) = ',' ) RETURNS @tableList TABLE ( value CHAR(1024) ) AS BEGIN DECLARE @value VARCHAR(1024) DECLARE @position INT SET @list = LTRIM(RTRIM(@list)) + ',' SET @position = CHARINDEX(@delimiter, @list, 1) IF REPLACE(@list, @delimiter, '') <> '' BEGIN WHILE @position > 0 BEGIN SET @value = LTRIM(RTRIM(LEFT(@list, @position - 1))); INSERT INTO @tableList ( value ) VALUES ( CAST(@value AS VARCHAR(1024)) ); SET @list = RIGHT(@list, LEN(@list) - @position); SET @position = CHARINDEX(@delimiter, @list, 1); END END RETURN END --This is function 2 Well the redgate code formatter is returning nothing when I paste in the code for the second function so here it is in plain - sorry it if is harder to read. CREATE FUNCTION dbo.fGetAppTypes ( @AppID VARCHAR (15) ) RETURNS VARCHAR(max) AS BEGIN DECLARE @id_list AS VARCHAR(max) DECLARE @Apptype VARCHAR(max)-- SET @Apptype='' DECLARE @idtable TABLE (ID VARCHAR(max)) --get the category list based ON the app id SET @id_list = (SELECT cast(u_application_type AS VARCHAR(max)) AS u_application_type FROM dbo.ApplicatiON WHERE u_number = @AppID) -- use the table valued function to parse the ids into a table. INSERT INTO @idTable(ID) SELECT Value FROM dbo.fParsing_Table(@id_list, ','); -- join the var table of ids to the table to query to get the good values -- use coalesce to concatenate the good values SELECT @Apptype = CASE WHEN coalesce(@Apptype + ',', '')=',' THEN coalesce(@Apptype + '', '') + T1.u_name ELSE coalesce(@Apptype + ',', '') + T1.u_name end FROM dbo.Apptype T1 JOIN @idTable T2 ON T1.sys_id =
T2.ID -- get the incorrectly stored values and use coalesce to concatenate to the good values SELECT @Apptype = CASE WHEN coalesce(@Apptype + ',', '')=',' THEN coalesce(@Apptype + '', '') + cast(
T2.ID AS VARCHAR(max)) ELSE coalesce(@Apptype + ',', '') + cast(
T2.ID AS VARCHAR(max)) end FROM @idTable T2 WHERE
T2.ID not in (SELECT cast(sys_id AS VARCHAR(max)) FROM dbo.Apptype) RETURN @Apptype END
Thomas, Thanks for responding - unfortunately it is all over the place. Here are some examples for format. id, name, id, id, id name, name, id, id, id id, name, name, id, id id, name, , , id id, id, id, id, id, name etc. I'm not sure what the limit is on the size, but I've seen as many as 9 elements within the field. I've seen the elements change as well.
14 People are following this question.
