Store hashbyte on field with length smaller than fields i concatenate it?

Transact-SQL
,
1

I work on sql server 2019 i have table have multi column string concatenation as StrSubstance,strmass ,strcasnumber

i need to create filed hashchemical with nvarchar(700) and this will store hash for 3 columns concatenation with each other

I need to using hashbyte because i need to compare
this 3 column from table StrSubstance,strmass ,strcasnumber
and search on another table to get id

are this possible

what i mean

alter table [dbo].[fmdchemical] add hashchemical nvarchar(700) null
    
 update ch  set ch.hashchemical =HASHBYTES('SHA2_512',concate(StrSubstance,strmass,strcasnumber)) from [dbo].[fmdchemical] ch

so are hashing will be correct with nvarchar(700) and every field have length 3500

are this will make issue on the feature with big counts

also are hash every column alone then compare it or concate all these columns on one column then compare

CREATE TABLE [dbo].[fmdchemical](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [chemicalid] [int] NULL,
      [StrSubstance] [nvarchar](3500) NULL,
      [strmass] [nvarchar](3500) NULL,
      [strcasnumber] [nvarchar](3500) NULL
  ) ON [PRIMARY]
        
  GO
  SET IDENTITY_INSERT [dbo].[fmdchemical] ON 
        
  INSERT [dbo].[fmdchemical] ([id], [chemicalid], [StrSubstance], [strmass], [strcasnumber]) VALUES (826807, 748787, N'3P04,AL2O3,C,C,Cr203,Cu,Cu,Fe,Mn,Others,others,others,Others,Pb,Si02,SiC,Silicone Resin,SiO2,Sn,Sn,Ti02', N'0.0004,0.0096,0.336,0.3992,0.5904,0.764,0.7904,1.5408,1.6176,1.6808,1.6808,2.9344,5.2392,7.2,7.9608,19.2072,32.08,65.3584,148.3384,232.7992,269.472', N'592910,592960,592961,593043,593043,593259,593290,593306,593306,593495,593501,593501,593505,593505,621442,621442,621442,621442,622089,624020,650559')
2

have you looked at the data? Do they all need to be nvarchar(3500)? What are the max lengths of each of those columns. For the concat part, I would add a delimiter of some sort (maybe a pipe) to separate them (concate(StrSubstance,'|',strmass,'|',strcasnumber,'|'). Otherwise you could be getting false values. For instance if StrSubstance = 12 and strmass = 3 and strcasnumber = 4, the concat = 1234. But what if StrSubstance = 1 and strmass = 23 and strcasnumber = 4, the contact would still be 1234

3

  1. SHA2_512 generates a 64 byte binary value, there's no reason to declare hashchemical as anything else.

  2. This UPDATE should do what you need:

update [fmdchemical] 
set hashchemical=HASHBYTES('SHA2_512',concat_ws(0x00,StrSubstance,strmass,strcasnumber))

Using 0x00 as a separator will address what @mike01 mentioned about delimiters, and avoid the problem of using a regular character delimiter that might appear in the columns.