[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 คือ อะไร 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

REPLICATE : เอาไว้เติมตัวอักษรที่ต้องการลงใน String ตามจำนวนครั้งที่กำหนด ตัวอย่างการใช้งาน เช่น

• เติมตัวอักษร '0' เพื่อเติมข้อมูลใน Column "Product Line" ให้ครบ

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

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

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

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

SELECT SECURITYCODE + REPLICATE(' ', 5) AS 'SECURITY CODE'
FROM INVEST.SECURITY

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

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

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

ใช้ SPACE

SPACE : เอาไว้เติม Space ลงใน String ตามจำนวนครั้งที่กำหนด ตัวอย่างการใช้งาน เช่น

• เติม Space เพื่อเติมข้อมูลระหว่าง Column "First name" กับ Column "Last name" ให้ครบ

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

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

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

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

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

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'