[SQL Server] เติม SPACE ให้ Column ใน Fixed Length Text File

Blog ตอนนี้ น่าจะดองมาปีกว่าๆ ตอนทำข้อมูลส่งระบบ PTI โดยจะต้องส่งเป็น Fixed Length Text File และทางเลือกที่ ผมใช้ทำ คือ การเขียน Store Procedure เพื่อส่งข้อมูลให้ Application ไปเขียน Text File ต่อไปครับ โดยก่อนที่ผมมแชร์ประสบการณ์ ผมขอเกริ่นนิดนึงกกันก่อนว่าเจ้า Fixed Length Text File คือ อะไร

Fixed Length Text File ที่มีการกำหนดรูปแบบแน่นอนแล้ว ว่าแต่ละ Column มีความยาวได้ไม่เกินกี่ตัวอักษร ถ้าข้อมูลใน Field นั้นๆไม่ครบ ระบบต้องเติม Space ลงไปให้ครบครับ แต่ถ้ามันเกินก็ต้องตัดออกนะครับ เพราะระบบมันจะมองเป็นอีก Field นึงแทน ตัวอย่าง เช่น ต้องการไฟล์ข้อมูลแบบ Fixed Length ที่มีกากำหนดความกว้างของข้อมูล ดังนี้

• Column ที่ 1 "Portfolio Code" ความยาวไม่เกิน 30 ตัวอักษร
• Column ที่ 2 "TX Type" ความยาวไม่เกิน 10 ตัวอักษร
• Column ที่ 3 "Broker" ความยาวไม่เกิน 15 ตัวอักษร
• Column ที่ 3 "Security" ความยาวไม่เกิน 15 ตัวอักษร
• Column ที่ 5 "Unit" ความยาวไม่เกิน 20 ตัวอักษร

จาก Spec ข้างต้น ผมเสกไฟล์ตัวอย่างข้อมูล ดังนี้ครับ

PORTFOLIO                    TX TYPE   BROKER         SECURITY       UNIT
-----------------------------------------------------------------------------------------                
PING_TRADE_EQ                BUY       SCBS           IFEC           10000               
PING_TRADE_EQ                BUY       SCBS           SVOA           9000                

แต่เวลาที่เราเอาไปดูใน Text Editor ต้องเลือก font ที่รองรับ Fixed Length (คือ ขนาดของตัวอักษรทุกตัว มีขนาดเท่ากัน เช่น Consolas เป้นต้นครับ) หลายอาจจะส่งสัยว่ามันเท่ากันจริง หรือป่าว อันนี้ผมได้ Capture รูป โดยกำหนดให้มันแสดง Space ใน Notepad++ ดังนี้ครับ

เกริ่นมาซะยาวแล้ว ผมของลองกับข้อมูลจริง ว่าเราจะเติม Space ลงไปในแต่ะ Field ให้ครบได้อย่างไร ส่วนกรณีที่ข้อมูลมันเกินนั้น สามารถใช้ Substring หรือ Left ตัดคำได้อยู่แล้วครับ โดยใน Blog นี้ผมขอเสนอคำสั่งใน SQL Server ที่ใช้ในการเติม SPACE ดังนี้ครับ

• REPLICATE : เอาไว้เติมตัวอักษรที่ต้องการลงใน String ตามจำนวนครั้งที่กำหนด ตัวอย่างการใช้งาน เช่น
  • เติมตัวอักษร '0' เพื่อเติมข้อมูลใน Column "Product Line" ให้ครบ

    [sql]
    SELECT REPLICATE('0', 4) + PRODUCTLINE AS 'PRODUCT LINE'
    FROM DS_POS.PRODUCT
    [/sql]

    ผลลัพธ์ที่ได้

    PRODUCT LINE
    ---------
    0000A 
    0000B 
    0000C 
    

  • เติม Space 5 ครั้ง เพื่อเติมข้อมูลใน Column "Security Code" ให้ครบ

    [sql]
    SELECT SECURITYCODE + REPLICATE(' ', 5) AS 'SECURITY CODE'
    FROM INVEST.SECURITY
    [/sql]

    SECURITY CODE
    ---------
    IFEC.....
    SVOA..... 
    

    หมายเหตุ ผมใช้จุด เพื่อแสดงให้เห็น Space ครับ

• SPACE : เอาไว้เติม Space ลงใน String ตามจำนวนครั้งที่กำหนด ตัวอย่างการใช้งาน เช่น
  • เติม Space เพื่อเติมข้อมูลระหว่าง Column "First name" กับ Column "Last name" ให้ครบ

    [sql]
    SELECT RTRIM(FIRSTNAME) + SPACE(2) + LTRIM(LASTNAME) AS 'ADMIN NAME'
    FROM INVEST.USER
    ORDER BY FIRSTNAME, LASTNAME;
    [/sql]

    ADMIN NAME
    ---------
    CHATRI..NGAMBENCHAWONG 
    

    หมายเหตุ ผมใช้จุด เพื่อแสดงให้เห็น Space ครับ

แถมอีกนิดนึงครับ SQL ที่ใช้ปรับข้อมูลใน แต่ละ Column ให้มีขนาดตามที่ Fix Length กำหนดครับ เผื่อจะลองนำไปประยุกตใช้กันครับ โดยใช้คำสั่ง REPLICATE เพื่อเติม SPACE ให้ครบตามที่กำหนดก่อน แล้วค่อยใช้คำสั่ง LEFT เพื่อตัดตัวอักษรตาม SPEC ของแต่ละ Field ครับ

[sql]
SELECT LEFT(PORT.PORFOLIOCODE + REPLICATE(' ',30),30) AS PORTFOLIO
, LEFT(INV.TXTYPE+ REPLICATE(' ',10),10) AS TXTYPE
, LEFT(BRO.BROKERCODE+ REPLICATE(' ',15),15) AS BROKER
, LEFT(SEC.SECURITYCODE+ REPLICATE(' ',15),15) AS SECURITY
, LEFT(INV.UNIT+ REPLICATE(' ',20),20) AS UNIT
FROM INVESTTX INV
LEFT OUTER JOIN PORTFOLIO PORT
ON INV.PORTFOLIOID = PORT.PORTFOLIOID
AND PORT.FLAG = 'A'
LEFT OUTER JOIN BROKER BRO
ON INV.BROKETID= BRO.BROKETID
AND BRO.FLAG = 'A'
LEFT OUTER JOIN SECURITY SEC
ON INV.SECURITYID= SEC.SECURITYID
AND SEC.FLAG = 'A'
[/sql]