How to delete record from 2 tables where one contains the left 0 SQL SERVER

Question

I have 2 tables in which one is inserted the records that comes from a file txt and the other the records that comes from a file excel.

With this, I need to delete the records from the table txt containing in the table excel, in case the same.

Only that there are files in the table txt starting with 0 and in the table excel no, and the system does not understand as equal and does not exclude.

How can I also delete records that contain 0 at the beginning ?

My delete is like this:

DELETE 
TEMP_IMPORTACAO_TXT 
FROM 
TEMP_IMPORTACAO_TXT TXT 
INNER JOIN 
TEMP_IMPORTACAO_EXCEL EX 
ON 
TXT.CAMPO = EX.CAMPO

Answer 1

as I don’t know what kind of data you have in this field, it can be a string that has only numeric characters or not.

Then you can use substring to remove the zeros on the left, for example:

DECLARE @texto varchar(max);

SET @texto = '00000001A6F2B';
print SUBSTRING(@texto, PATINDEX('%[^0]%',@texto), LEN(@texto) - PATINDEX('%[^0]%',@texto) + 1)
--1A6F2B

your updated select would look like this:

DELETE 
TEMP_IMPORTACAO_TXT 
FROM 
TEMP_IMPORTACAO_TXT TXT 
INNER JOIN 
TEMP_IMPORTACAO_EXCEL EX 
ON 
SUBSTRING(TXT.CAMPO, PATINDEX('%[^0]%',TXT.CAMPO), LEN(TXT.CAMPO) - PATINDEX('%[^0]%',TXT.CAMPO) + 1) = EX.CAMPO

The only problem would be if the field has only '0', in this case you will need to make use of a case.

CASE WHEN PATINDEX('%[^0]%',@texto) = 0
    THEN '0'
    ELSE SUBSTRING(@texto, PATINDEX('%[^0]%',@texto), LEN(@texto) - PATINDEX('%[^0]%',@texto) + 1)
END