help with data type and default value

Question

help with data type and default value

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

John Oliver Old Hand Points: 351 More actions · Answer 1

fisher08 (6/27/2008)
John Oliver (6/25/2008)
Agree with Stephanie and Jeff, but only on the condition that length of the data is always going to be the same.
If you know that the data is always going to be 5 digits long, then as stated previously, store them as INT.
When you need to export them, format the data.
If, on the other hand, the field length is not always 5 digits, then you will have to store the formatted data as you get it.
In which case, you will have to define either a VARCHAR or NVARCHAR data field.
The field will not change, if its 5 digits, it stays that way, I need to export this data on a monthly basis and we are taking about thousands of records, I haven't found an easy way to format after exporting that doesn't involve manually editing the file.
BUT, this month is done and I have another month to figure that out...:)

Sort of agree with the code posted by Kaushal, but isn't this a little complex?

Combining the solution posted by Kaushal and that from Jeff (Posted 6/22/2008 4:08 AM +10GMT) yeilds

-- Create a table with an Integer column

CREATE TABLE DBO.STUFFED (COL1 INT);

-- Insert some Test Data

INSERT INTO DBO.STUFFED VALUES (1);

INSERT INTO DBO.STUFFED VALUES (11);

INSERT INTO DBO.STUFFED VALUES (111);

INSERT INTO DBO.STUFFED VALUES (1111);

INSERT INTO DBO.STUFFED VALUES (11111);

-- Output the data in Char format with leading zeros, I assumed an output field length of 15, you can change that to suit your need

SELECT

RIGHT(REPLICATE('0', 5) + CONVERT(VARCHAR(5), COL1), 5)

FROM DBO.STUFFED

The code above will store the values in their INT format.

To return the formatted data, the data is converted to a VARCHAR, appended to a string of five zeros (providing the zero padding) and then stripping all but the right-most 5 characters from the value.

ie.

For the INT value 142.

CONVERT(VARCHAR(5), COL1) = '142'

REPLICATE('0', 5) + CONVERT(VARCHAR(5), COL1) = '00000142'

RIGHT(REPLICATE('0', 5) + CONVERT(VARCHAR(5), COL1), 5) = '00142'

You could even create a padding function that will take in the length of the final string.

Something you have to consider with this solution is what happens with the submitted value is greater than the string length.

123456 to be padded to a string of 5 characters.

-- John Oliver
Sometimes banging your head against a wall is the only solution.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

fisher08 (6/27/2008)
Its the partners specs not ours, we are working for them in a way or else they will go elsewhere...that's why don't ask why...:)

In that case, I'd ask "Why" every 15 minutes... I don't want a partner that can't answer the "Why" and I don't want a partner that get's pissy if I ask. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)