help with data type and default value

Question

help with data type and default value

fisher08

Grasshopper

Points: 11
More actions
June 21, 2008 at 12:41 pm

#193085

Hello all,
I am looking to create a few fields that have a number of 0's in front, example 00100, 00012, etc.
I am not sure which type to use to keep the heading zero's in place.
Anyone have any ideas which might work best?
Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Normally, you wouldn't store them that way, you would just store them as some numeric and than leave the leading zeros as a task for presentation formatting.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Or you can store as varchar. Are these part numbers or something? If you don't do addition, or even if you do, I might store as character data.

fisher08 Grasshopper Points: 11 More actions · Answer 3

I am trying to simply things, we need to export the values and they need to retain the zero's in front.

Is there no way to store values this way?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Yes, as Steve said store them as strings (char or varchar) and just fill out the strings.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

No, NO, NO!!! Never ever store formatted data! You will pay dearly for doing so someday in the future. This type of (leading zero) formatting should really be done in the GUI! If the GUI boys can't figure it out or you don't have a GUI, then, yes, you can display them in SQL... for example... you can have a bunch of integers in a table... and make a calculated column to display them... like this...

DROP TABLE #MyHead

go

--===== Create a test table for the demo

CREATE TABLE #MyHead

(SomeInt INT,

ToDisplay AS RIGHT('0000000000'+RIGHT(SomeInt,10),10))

--===== Add some test data

INSERT INTO #MyHead

(SomeInt)

SELECT 1 UNION ALL

SELECT 10 UNION ALL

SELECT 100 UNION ALL

SELECT 1000 UNION ALL

SELECT 10000

--===== Show the real data and the display data

SELECT *

FROM #MyHead

Or, just select it using the formula... but don't ever store it that way in the database! 😉

--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)

fisher08 Grasshopper Points: 11 More actions · Answer 6

ok maybe I should give a bit more info.

I am designing a table that I need to export to integrate with a partner. This partner has very specific guidelines that I am told we need to follow 100%.

There are about 20 columns in total and five of them involved data with a number of zeros in front, don't ask why, I have no idea.

So basically when the data is entered every month, I am to export into a csv file and upload to the partner, we are talking thousands of records and I do not want to edit the file myself.

There is no gui, the data is imported by someone else.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 7

I've done this before. Just create the columns to export as Jeff wrote and export those to the other application. That way you don't mess up your own data and the other application gets what it wants - best of both worlds.

Todd Fifield

Stephanie Giovannini SSCertifiable Points: 7422 More actions · Answer 8

fisher08 (6/23/2008)
There are about 20 columns in total and five of them involved data with a number of zeros in front, don't ask why, I have no idea.

Why?

You should ask why. Find out what it is -> determine its range -> determine its data type. Also, you will want to know how it is used/stored by your partner.

Here's a hypothetical example:

You are given a column of data with values like: '00300', '04589', '34563'. You are told this is a product code and the values are supposed to be 5-digit integers with leading zeroes. So you figure you can safely store these values as INT and format the leading zeroes on export.

But you don't know how the partner is storing that code nor how the business rules are enforced on it. Maybe somebody there decides that they want to split the code '00300' into two and they find that "the system" allows '00300a' and '00300b' because the data is treated only as character data. When your system gets this data and tries to push '00300a' and '00300b' into an INT column, it's going to choke.

Depending on the balance of power in this business relationship, either you have to change your column's data type (and all its dependencies) or you tell the partner, "you can't do that, change the data." Given the tone of your posts, I suspect the former.

CYA and find out why.

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

I agree with Stephanie... you really need to find out both the what and the why... if they intend to use only numerics with leading zero's ... forever... then store integers and format the data when you make the file. Otherwise, to meet their immutable requirement, you may end up having to store formatted data...

--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)

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

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.

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

Kaushal SSC Enthusiast Points: 118 More actions · Answer 11

I would not recommend storing formatted data in the database. If the data elements you are storing are of integer types, you can create a View that right justifies the numeric field with leading zeros. Here is a sample of what you could do.

-- 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 REPLICATE('0',(15-LEN(CAST(COL1 AS VARCHAR(15)))))

+ SUBSTRING(CAST(COL1 AS VARCHAR(15)),1,LEN(CAST(COL1 AS VARCHAR(15)))) AS COL1_STUFFED

FROM DBO.STUFFED

... and the result is as follows:

000000000000001

000000000000011

000000000000111

000000000001111

000000000011111

fisher08 Grasshopper Points: 11 More actions · Answer 12

Stephanie Giovannini (6/23/2008)
fisher08 (6/23/2008)
There are about 20 columns in total and five of them involved data with a number of zeros in front, don't ask why, I have no idea.
Why?
You should ask why. Find out what it is -> determine its range -> determine its data type. Also, you will want to know how it is used/stored by your partner.

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...:)

fisher08 Grasshopper Points: 11 More actions · Answer 13

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...:)

Stephanie Giovannini SSCertifiable Points: 7422 More actions · Answer 14

fisher08 (6/27/2008)
Stephanie Giovannini (6/23/2008)
fisher08 (6/23/2008)
There are about 20 columns in total and five of them involved data with a number of zeros in front, don't ask why, I have no idea.
Why?
You should ask why. Find out what it is -> determine its range -> determine its data type. Also, you will want to know how it is used/stored by your partner.
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...:)

I understand that. Painfully so from experience. Sometimes I just allow myself to think there's an ideal world out there, maybe just not where I can see it...