How to insert right justifies leading zeros

I have a business rule in my environment where I need to insert right justified leading zeros in the column. For example if the value to be inserted is 12 than it should be inserted as 0000012. How can I do this

Chintan.

Here it is:

SELECT RIGHT('000000' + CAST(12 as SYSNAME), 7)

Thanks,
Zuomin
|||

The "leading zeroes" issue might be more of a display issue than a storage issue. Is your column a character field, integer field, or what exactly?

If your field is character (or varchar) then you can use concatenation and the RIGHT function to pre-pend your string with zero characters; however, if your field is integer understand that it is stored as a binary encoded integer and the zeroes are understood.

You can do something like zuomin has suggested for display purposes. Also, if you MUST carry this as a zero-filled key, you can store it in the table as a computed column -- again as a CHAR field similar to zuomin's suggestion.

|||

the rule applies to about 9600 rows so....what can be the best solution

Chintan

|||

as of now the columns are in Varchar, but this whole table is getting converted to Flatfile of fixed width and than it is submitted to client. In this case wot shud I allocate the column as Varchar or Integer taking flatfile into consideration.

|||

OK, but what is the datatype of your column?

|||

Varchar.

|||

I would suggest using something like zuomin's RIGHT solution.

|||

A bit of editorial: I have never really liked doing this. This makes it possible to have many different '1' records. Now it is possible to have record '1', '01', '001', etc as valid primary keys to this table. And I have seen this problem manifest several times. For me, I would rather store the integer field and have the application do the formatting.

|||

the problem here is 1. there is no primary keys

2. there is no application which will insert leading zeros

3. it is goin as a simple text files

so now if i use

SELECT RIGHT('000000' + CAST(trtyc as SYSNAME), 7)

this is just a select statement, but i need to insert leading zeros into the db so tht whenever the data goes in to flat files instead of showing 12 it will show 0000012

it might some kinda update statment

Chintan

|||

UPDATE yourTable SET yourVarCharColumn= RIGHT('000000'+yourVarCharColumn,7)

Without WHERE condtion, you will update all your records.