Removing space and all other special character to get match

Question

There are 2 tables. Parent table and Child table.

The key column of both the table are of Varchar, with very very long values in it with many spaces and special characters.

How can I remove the spaces and special characters and create the Hash Value out of it.

DDL:
CREATE TABLE Parent
(
Trackid VARCHAR(MAX),
ParentDesc varchar (MAX)
)
Insert Parent
SELECT 'B821F23F87195F7E8D8940302B9F23CE2023-04-15 00:42:17.948CLICK','OUTLET: NOS SOLDES SAISONNIERES SE TERMINENT BIENTOT ! JUSQU'A 40 % DE REMISE EN MAGASIN ET EN LIGNE A: HTTP://VIB.ES/B11AHV STOP AU 36110' UNION ALL
SELECT '67BD6D6BFC24DB0C4F6EB42636D4AD122023-05-01 10:07:26.386CLICK','OUTLET: IT'S YOUR BIRTHDAY MONTH! VISIT ANY OF OUR STORES TO REDEEM YOUR 20 POUND GIFT CARD. HTTP://VIB.ES/21YBX2 STOP: HTTP://VIB.ES/H1ABQ3 .' UNION ALL
SELECT '9E29E7E18CFAF5A0D5645B2E109CD8AD2023-05-01 10:07:26.047CLICK','OUTLET: E IL MESE DEL TUO COMPLEANNO! VISITA I NOSTRI NEGOZI PER RICEVERE LA TUA GIFT CARD DA 20 CHF. HTTP://VIB.ES/B1IBK2 DISISCRIVITI HTTP://VIB.ES/Z1IBQ3 .' UNION ALL
SELECT 'FB49969FD8236B3C016BA11D2CE0D0022023-05-11 13:24:26.807CLICK','OUTLET: IT'S YOUR BIRTHDAY MONTH! VISIT ANY OF OUR STORES TO REDEEM YOUR 20 POUND GIFT CARD. HTTP://VIB.ES/51KBI2 STOP: HTTP://VIB.ES/Q1WBL2 .' UNION ALL
SELECT 'AF0CBD09292B46DC2FF4ED50B3B254E32023-05-04 08:15:24.402CLICK','OUTLET: IT'S YOUR BIRTHDAY MONTH! VISIT ANY OF OUR STORES TO REDEEM YOUR 20 POUND GIFT CARD. HTTP://VIB.ES/E1ZB53 STOP: HTTP://VIB.ES/P16B53 .' UNION ALL
SELECT '654FE9592107878FB6043276AFD254872023-05-01 10:07:23.483CLICK','OUTLET: ESTE MES ES SU CUMPLEANOS! VISITE NUESTRAS TIENDAS PARA CANJEAR SU TARJETA REGALO DE 20 EUROS. HTTP://VIB.ES/Z1FBK3 STOP: HTTP://VIB.ES/X1JBT3' UNION ALL
SELECT '9EDB1CDB1672BA96D69AE0D29BB742512023-05-01 17:47:53.519CLICK','OUTLET: ESTE MES ES SU CUMPLEANOS! VISITE NUESTRAS TIENDAS PARA CANJEAR SU TARJETA REGALO DE 20 EUROS. HTTP://VIB.ES/T1CBD3 STOP: HTTP://VIB.ES/G15BF3' UNION ALL
SELECT '756E8370556CE1F53E94A6439E9C79A62023-04-19 03:54:19.306CLICK','OUTLET: OUR SEASONAL REDUCTIONS END SOON! UP TO 40% OFF IN-STORE AND ONLINE AT: HTTP://VIB.ES/Q1FBB0 STOP: HTTP://VIB.ES/C1VB10' UNION ALL
SELECT 'E96596F04AC7CC43E896D5C49D59BAA82023-04-19 05:44:19.328CLICK','OUTLET: UNSERE SAISONALEN PREISNACHLAESSE ENDEN BALD! BIS ZU 40 % PREISNACHLASS IM STORE UND ONLINE BEI: HTTP://VIB.ES/T1DA7U STOP: HTTP://VIB.ES/B1PAQV'
CREATE TABLE Child
(
Trackid VARCHAR(MAX),
ChildDesc varchar (MAX)
)
Insert Child
SELECT 'B821F23F87195F7E8D8940302B9F23CE2023-04-15 00:42:17.948CLICK','OUTLET: NOS SOLDES SAISONNIERES SE TERMINENT BIENTOT   ! JUSQU'A 40 % DE REMISE EN MAGASIN ET EN LIGNE A: HTTP://VIB.ES/B11AHV STOP AU 36110' UNION ALL
SELECT '67BD6D6BFC24DB0C4F6EB42636D4AD122023-05-01 10:07:26.386CLICK','OUTLET: IT'S YOUR $$  BIRTHDAY MONTH! VISIT ANY OF OUR STORES TO REDEEM YOUR 20 POUND GIFT CARD. HTTP://VIB.ES/21YBX2 STOP: HTTP://VIB.ES/H1ABQ3 .' UNION ALL
SELECT '9E29E7E18CFAF5A0D5645B2E109CD8AD2023-05-01 10:07:26.047CLICK','OUTLET: E IL MESE DEL TUO COMPLEANNO! VISITA I NOSTRI NEGOZI PER RICEVERE LA TUA GIFT CARD DA 20 CHF. HTTP://VIB.ES/B1IBK2 DISISCRIVITI HTTP://VIB.ES/Z1IBQ3 .' UNION ALL
SELECT 'FB49969FD8236B3C016BA11D2CE0D0022023-05-11 13:24:26.807CLICK','OUTLET: IT'S YOUR BIRTHDAY MONTH! VISIT ANY OF OUR STORES TO REDEEM YOUR 20 POUND GIFT CARD. HTTP://VIB.ES/51KBI2 STOP: HTTP://VIB.ES/Q1WBL2 .' UNION ALL
SELECT 'AF0CBD09292B46DC2FF4ED50B3B254E32023-05-04 08:15:24.402CLICK','OUTLET: IT'S YOUR    BIRTHDAY MONTH! VISIT ANY OF OUR STORES TO REDEEM YOUR 20 POUND GIFT CARD. HTTP://VIB.ES/E1ZB53 STOP: HTTP://VIB.ES/P16B53 .' UNION ALL
SELECT '654FE9592107878FB6043276AFD254872023-05-01 10:07:23.483CLICK','OUTLET: ESTE MES ES SU CUMPLEANOS! VISITE NUESTRAS TIENDAS PARA CANJEAR SU TARJETA REGALO DE 20 EUROS. HTTP://VIB.ES/Z1FBK3 STOP: HTTP://VIB.ES/X1JBT3' UNION ALL
SELECT '9EDB1CDB1672BA96D69AE0D29BB742512023-05-01 17:47:53.519CLICK','OUTLET: ESTE MES ES SU CUMPLEANOS! VISITE NUESTRAS TIENDAS PARA CANJEAR SU TARJETA REGALO DE 20 EUROS. HTTP://VIB.ES/T1CBD3 STOP: HTTP://VIB.ES/G15BF3' UNION ALL
SELECT '756E8370556CE1F53E94A6439E9C79A62023-04-19 03:54:19.306CLICK','OUTLET: OUR SEASONAL REDUCTIONS END SOON! UP TO 40% OFF IN-STORE AND ONLINE AT: HTTP://VIB.ES/Q1FBB0 STOP: HTTP://VIB.ES/C1VB10' UNION ALL
SELECT 'E96596F04AC7CC43E896D5C49D59BAA82023-04-19 05:44:19.328CLICK','OUTLET: UNSERE SAISONALEN PREISNACHLAESSE ENDEN BALD! BIS ZU 40& % PREISNACHLASS IM STORE UND ONLINE BEI: HTTP://VIB.ES/T1DA7U STOP: HTTP://VIB.ES/B1PAQV'

Expected Output:

example:-

HashKey|Remarks

100x | Match

210xy |Not Match

Please help.

Answer 1

Hi @Rosalina5 ,

On stackoverflow I found a function to remove all special characters:

CREATE OR ALTER FUNCTION [dbo].[RemoveNonAlphaCharacters](@Temp VARCHAR(max))

To convert the string to a hash, you can use the HASHBYTES function:
https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver16

This will result in a query like:

WITH tmp_parent as 
(
SELECT  HASHBYTES('SHA2_256',dbo.RemoveNonAlphaCharacters(Trackid)) as HashKey
FROM	Parent
)
, tmp_child as
(
SELECT  HASHBYTES('SHA2_256',dbo.RemoveNonAlphaCharacters(Trackid)) as HashKey
FROM	child
)

SELECT	p.HashKey,
		IIF(c.HashKey is null,'No child', 'Has child') as Has_Child
FROM	tmp_parent p
LEFT JOIN tmp_child c
ON		p.HashKey = c.HashKey

Because I didn't know the logic for the Remarks column I added a column which indicates if there is a child record. So, you may need to change this part.

Kind regards,

Wilko

---

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".