go backsqlschool blogs list

Add/Remove Leading/Trailing Zero with T-SQL and SSIS

by Antonios Chatzipavlis

Στο περιβάλλον εργασίας μου υπάρχουν αρκετές διαδικασίες που ανταλλάσσουν  δεδομένα μεταξύ ετερογενών συστημάτων. Κάποια από αυτά είναι 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*/


Ημερομηνία: 18 July 2014 14:08
Share it:

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS