Zeros - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Zeros

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-27 : 14:05:16

I'm adding a table for one of our centers that has data with four digit numerics in two of the columns. Ie Column A = 4510, 4403, 4420. And Column B = 1000, 2000, 3000.

In column B some of the values are 0000 which I need to have for a JOIN with another table. Each time I load the new table data from Excel or Access the 0000 turns into just one 0.

For the life of me I can't figure out a way to format Column B so that it shows 0000.

Thanks.

GC

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-04-27 : 14:09:17

make it a CHAR instead of an INT

-ec

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-27 : 14:50:43

EC,

I originally had the format set to INT, but after digging into my SQL book yesterday had changed the column to CHAR without any luck. I tried it again just now and still nothing. I've made sure that the columns in Excel are formatted in the Custom setting as 0000. I dumped the old data and reloaded the table again and it still shows only one 0. If I open up the table to display all the rows and try to make an adjustment manually I get an error message. "The value you entered is not consistent with the data type or length of the column."

I have the column set as CHAR and the length at 4.

GC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-27 : 14:55:11

open up the table? EM?

Use Query Analyzer to fix the data:

UPDATE Table1
SET Column1 = '0000'
WHERE Column1 = '0'

Tara

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-27 : 15:07:02

Tara,

Thanks, that did it! All "0" are now "0000". I did forget to mention that I have just one row where the value should be 0005, and instead is still showing 5. Can I use the same update placing a 5 in the there, or will it alter all the other 5's I have? Ie 2500 would change to 2000500.

Thanks again.

GC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-27 : 16:03:27

UPDATE Table1
SET Column1 = '0005'
WHERE Column1 = '5'

It only finds exact matches.

Tara

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-27 : 16:17:59

Tara,

That did it.

Many thanks for all your help as always.

GC

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-28 : 12:37:19

You might want to try...


USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
GO

INSERT INTO myTable99(Col1)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 650
GO

SELECT * FROM myTable99

ALTER TABLE myTable99 ALTER COLUMN Col1 CHAR(4)
GO

SELECT * FROM myTable99

--Why don't these work?
SELECT RIGHT(REPLICATE('0',4)+Col1,4) FROM myTable99
SELECT REVERSE(SUBSTRING(REVERSE('0000'+Col1),1,4)) FROM myTable99
GO

Never mind....what a scrub

--This worked
UPDATE myTable99 SET Col1 = RIGHT(RTRIM(REPLICATE('0',4)+Col1),4)

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Brett

8-)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-28 : 12:55:04

quote:
Originally posted by X002548

UPDATE myTable99 SET Col1 = RIGHT(RTRIM(REPLICATE('0',4)+Col1),4)

This will also do it:
UPDATE mytable99 SET Col1 = RIGHT('0000' + RTRIM(Col1), 4)

Slightly shorter

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources