When writing select statements to hash data from multiple columns we usually have to repeat code over and over again for each column. Things like “trim”, “isnull” etc. Is there a way to create a custom function that would do this for a list of strings or columns?

Example code:

 with example_data as (
 select '1' as id, 'Jon ' as [name], 'Doe' as [lastname]
 union all
 select '2' as id, 'Joe' as [name], 'Doe' as [lastname]
 union all 
 select '3' as id, 'Jane' as [name], 'Doe' as [lastname]
 )

 select *, 
      convert(varbinary(32),hashbytes('SHA2_256',
      concat(
      convert(varchar(500),trim(isnull([id],''))),
      convert(varchar(500),trim(isnull([name],''))),
      convert(varchar(500),trim(isnull([lastname],'')))
      ))) as hash_column
 from example_data
      

Im looking for a way to build a custom function that abstracts away the need of writing “convert”, “trim”, “isnull”, choosing hashing algorithm etc. The end result would look something like this:

 select *, 
      my_custom_hash_function('id,'name','lastname') as hash_column
 from example_data

So far I’ve looked at user defined functions but very confused on how or if it’s possible to make it work for this use case.