question

smdrzal avatar image
smdrzal 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
stored-procedurestsqlfunctions
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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
0 Likes 0 ·
smdrzal avatar image smdrzal commented ·
I have arrived at a satisfactory method. However I am getting an odd truncation (at 32 chars) on some of the data. I'd like to post my code, but I am not seeing the red speech bubble to have it formatted correctly...
0 Likes 0 ·
smdrzal avatar image smdrzal commented ·
Found the truncation and fixed that. This function based approach works exactly as needed. Guess I'm solving my own issue.
0 Likes 0 ·
smdrzal avatar image
smdrzal answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

smdrzal avatar image
smdrzal answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.