B.Chintan


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.





Re: How to insert right justifies leading zeros

zuomin


Here it is:

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


Thanks,
Zuomin





Re: How to insert right justifies leading zeros

Kent Waldrop Se07

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.






Re: How to insert right justifies leading zeros

B.Chintan

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

Chintan






Re: How to insert right justifies leading zeros

B.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.






Re: How to insert right justifies leading zeros

Kent Waldrop Se07

OK, but what is the datatype of your column





Re: How to insert right justifies leading zeros

B.Chintan

Varchar.






Re: How to insert right justifies leading zeros

Kent Waldrop Se07

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





Re: How to insert right justifies leading zeros

Kent Waldrop Se07

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.





Re: How to insert right justifies leading zeros

B.Chintan

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






Re: How to insert right justifies leading zeros

limno

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

Without WHERE condtion, you will update all your records.