Στο περιβάλλον εργασίας μου υπάρχουν αρκετές διαδικασίες που ανταλλάσσουν δεδομένα μεταξύ ετερογενών συστημάτων. Κάποια από αυτά είναι legacy και ενώ έχει γίνει μια φανταστική δουλειά να μιλάνε τα συστήματα αυτά μέσω services, εντούτοις υπάρχουν κάποιες μικρές ιδιαιτερότητες. Μια από αυτές είναι με τα νούμερα που τα legacy θέλουν συγκεκριμένο πλήθος ψηφίων με leading zero και φυσικά δίνουν τα αποτελέσματα με αυτό το format.
Επειδή όμως αυτό είναι κάτι που αρκετοί το κάνουν με διάφορους τρόπους μέσα από τον κώδικα της εφαρμογής τους θέλω με αυτό το post να δείξω πως γίνεται με απλό T-SQL αλλά να πάμε και ένα βήμα παραπάνω καθώς υπάρχουν και αρκετοί που χρησιμοποιούν Integration Services για να κάνουν ETL διαδικασίες.
T-SQL
-- ADD LEADING OR TRAILING ZERO
-- THE RESULT IS STRING
DECLARE @NUM DECIMAL(15,2) = 1234.56;
SELECT RIGHT(N'000000000000000'+CAST(@NUM AS NVARCHAR(15)),15) AS LEADING_ZERO
,LEFT(CAST(@NUM AS NVARCHAR(15))+N'000000000000000',15) AS TRAILING_ZERO;
GO
-- REMOVE LEADING OR TRAILING ZERO
-- THE RESULT IS STRING
DECLARE @LNUM NVARCHAR(15) = '000000001234.56';
DECLARE @TNUM NVARCHAR(15) = '1234.5600000000';
SELECT SUBSTRING(@LNUM,PATINDEX('%[^0]%',@LNUM),15) AS NO_LEADING_ZERO
,REVERSE(SUBSTRING(REVERSE(@TNUM),PATINDEX('%[^0]%',REVERSE(@TNUM)),15)) AS NO_TRAILING_ZERO
-- CHECK FOR NOT A DECIMAL NUMBER (SQL SERVER 2000, 2005, 2008, 2008 R2 VERSION)
,CASE WHEN CHARINDEX('.',@TNUM) = 0 THEN @TNUM
ELSE REVERSE(SUBSTRING(REVERSE(@TNUM),PATINDEX('%[^0]%',REVERSE(@TNUM)),15)) END AS NO_TRAILING_ZERO_2
-- CHECK FOR NOT A DECIMAL NUMBER (SQL SERVER 2012, 2014 VERSION)
,IIF (CHARINDEX('.',@TNUM) = 0,@TNUM,REVERSE(SUBSTRING(REVERSE(@TNUM),PATINDEX('%[^0]%',REVERSE(@TNUM)),15))) AS NO_TRAILING_ZERO_3
SSIS
Με τη χρήση ενός Derived Column task το expression για να γίνει εισαγωγή leading & trailing zero είναι
RIGHT("000000000000000" + ((DT_WSTR,15)(DT_DECIMAL,2)[ColumnName]),15) -- Leading
LEFT(((DT_WSTR,15)(DT_DECIMAL,2)[ColumnName]) + "000000000000000",15) -- Trailing
, ενώ για να αφαιρεθούν είναι
(DT_WSTR,50)(DT_DECIMAL,2)[ColumnName] -- Leading
REVERSE((DT_WSTR,50)REVERSE((DT_WSTR,50)(DT_DECIMAL,2)[ColumnName])) -- Trailing
/*antonch*/