CODEWITHSUNDEEP
mysqlsqlstored-procedures
Miss Song
Miss Song

Reputation: 41

How to add trailing space after some number of character (varchar) using sql stored procedure?

I need to display output data, I need to add/replace with white spaces with fix length for each column. Each column may got different length.

select  top (20)        
        patname + REPLICATE(' ', 30 - DATALENGTH(patname)) AS NAME,
        RefNo + REPLICATE(' ', 15 - DATALENGTH(RefNo)) AS REFNO,
        ClaimAmt AS AMOUNT    
    from  AR_Ebilling

Field length need to be set:

NAME = 30 varchar, REFNO = 15 varchar, AMOUNT = 10 money/decimal,

Expected Output Result :

NAME                           REFNO           AMOUNT
Ahmad Kasan                    1235            00000000565.93               
Amirah                         AY582M8D       -00023441200.23                   
Paul                           0ST127          00000004234.45

TQ

Upvotes: 1

Views: 881

Answers (1)

e4c5
e4c5

Reputation: 53774

The answer is that don't! One does not store redundant data in databases. All those white spaces are not really required to be stored because it's a trivial matter to pad out a string in your favourite programming language or even with in mysql itself. Towards this end mysql has :

RPAD

Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

And

LPAD

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

Also note that needlessly storing padded strings in the table makes it futile to use VARCHAR.

Upvotes: 1

Related Questions