How to delete record of 2 tables being one containing 0 to left SQL SERVER

Question

How to delete record of 2 tables being one containing 0 to left SQL SERVER

Navigation

#1 by (1 votes)

0

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

With this, I need to delete the records of the txt table that contain the excel table, in the case of the same ones.

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

How can I make it also delete records containing 0 in the beginning?

My delete looks like this:

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

sql sql-server

asked by anonymous 20.04.2016 / 16:58

1 answer

Java Connection mySQL JDBC Swift - Indicator activity is not showing up

score 1 · Accepted Answer

As I do not know what kind of data you have in this field, it may be a string that only has numeric characters or not.

Then you can use the substring to remove the leading zeros, 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 need to use a case.

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