question

This answer will show you a couple of techniques to be used here, and once you are comfortable with them you can make the answer a little shorter, but I've purposefully laid this out in distinct steps for now. First is to split the comma-separated values out into actual values, using a tally table (a cte one in this case). In order to do that I needed to add a comma to both the beginning and end of the string so I reference the base table, add those and do that in a cte called YourTable_AddedDelimiters. Once I have the values it's a simple case of using the CHAR() function to get the character values. All of this I do in another cte, SplitAndChar. I also assumed there is something in your base data that uniquely identifies each row. This is needed later to pull the string back together. declare @Yourtable table (SomeUniqueID int, YourField varchar(max)) insert into @Yourtable (SomeUniqueID, YourField ) select 1,'80, 104, 105, 108, 105, 112, 115, 32, 50, 48, 50, 69, 76'; with YourTable_AddedDelimiters (SomeUniqueID, YourField_AddedDelimitier) as (select SomeUniqueID, ','+YourField+',' from @Yourtable) --tally table cte , Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ), Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ), Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) , tally_cte as ( select n FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) D ( n ) where n < 500 ) , SplitAndChar as ( select SomeUniqueID, cast(substring(YourField_AddedDelimitier,N+1,charindex(',',YourField_AddedDelimitier,N+1)-N-1) as int) as Value, char(cast(substring(YourField_AddedDelimitier,N+1,charindex(',',YourField_AddedDelimitier,N+1)-N-1) as int)) as CharacterValue from tally_cte join YourTable_AddedDelimiters on N < len(YourField_AddedDelimitier) and substring(YourField_AddedDelimitier,N,1) = ',' ) If you select from SplitAndChar you will see the intermediate results select * from SplitAndChar SomeUniqueID Value CharacterValue ------------ ----------- -------------- 1 80 P 1 104 h 1 105 i 1 108 l 1 105 i 1 112 p 1 115 s 1 32 1 50 2 1 48 0 1 50 2 1 69 E 1 76 L To get this back to a 'string', we can use a well known trick with `xml path` select distinct SomeUniqueID , ( SELECT '' + isnull(CharacterValue , ' ') FROM SplitAndChar TABLE1 WHERE TABLE1.SomeUniqueID = TABLE2.SomeUniqueID for xml path(''), type ).value('.','varchar(max)') FROM SplitAndChar TABLE2 which gives SomeUniqueID ------------ ------------------------------------- 1 Philips 202EL ---------- So the full code (following the comments and relevant editing to handle dupes and missing values) is declare @Yourtable table (ResourceID int, UserFriendlyName0 varchar(max)) insert into @Yourtable (ResourceID, UserFriendlyName0 ) select 16793921, null; insert into @Yourtable (ResourceID, UserFriendlyName0 ) select 16800836, '80, 104, 105, 108, 105, 112, 115, 32, 50, 48, 50, 69, 76'; insert into @Yourtable (ResourceID, UserFriendlyName0 ) select 16800836, null; insert into @Yourtable (ResourceID, UserFriendlyName0 ) select 16801721, null; insert into @Yourtable (ResourceID, UserFriendlyName0 ) select 16802065, '80, 104, 105, 108, 105, 112, 115, 32, 50, 48, 50, 69, 76'; insert into @Yourtable (ResourceID, UserFriendlyName0 ) select 22222222, '107, 101, 118, 32, 116, 101, 115, 116, 32, 49'; insert into @Yourtable (ResourceID, UserFriendlyName0 ) select 22222222, '107, 101, 118, 32, 116, 101, 115, 116, 32, 50'; with YourTable_AddedUniquifier (UniqueID, ResourceID, UserFriendlyName0) as (select row_number()over(order by ResourceID), ResourceID, UserFriendlyName0 from @Yourtable) ,YourTable_AddedDelimiters (UniqueID, ResourceID, UserFriendlyName0_AddedDelimitier) as (select UniqueID, ResourceID, ','+UserFriendlyName0+',' from YourTable_AddedUniquifier) --tally table cte , Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ), Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ), Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) , tally_cte as ( select n FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) D ( n ) where n < 500 ) , SplitAndChar as ( select UniqueID, ResourceID, cast(substring(UserFriendlyName0_AddedDelimitier,N+1,charindex(',',UserFriendlyName0_AddedDelimitier,N+1)-N-1) as int) as Value, char(cast(substring(UserFriendlyName0_AddedDelimitier,N+1,charindex(',',UserFriendlyName0_AddedDelimitier,N+1)-N-1) as int)) as CharacterValue from tally_cte join YourTable_AddedDelimiters on N < len(UserFriendlyName0_AddedDelimitier) and substring(UserFriendlyName0_AddedDelimitier,N,1) = ',' ) --select * from YourTable_AddedDelimiters select --YourTable_AddedUniquifier.UniqueID, YourTable_AddedUniquifier.ResourceID, isnull(OnesWithValues.Friendly, 'Unknown') as Friendly from ( select distinct UniqueID, ResourceID , ( SELECT '' + isnull(CharacterValue , ' ') FROM SplitAndChar TABLE1 WHERE TABLE1.UniqueID = TABLE2.UniqueID for xml path(''), type ).value('.','varchar(max)') as Friendly FROM SplitAndChar TABLE2 ) OnesWithValues right join YourTable_AddedUniquifier on YourTable_AddedUniquifier.UniqueID = OnesWithValues.UniqueID which gives ResourceID Friendly ----------- --------------------------------- 16793921 Unknown 16800836 Philips 202EL 16800836 Unknown 16801721 Unknown 16802065 Philips 202EL 22222222 kev test 1 22222222 kev test 2 (7 row(s) affected)