Re format a fields values - 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

General SQL Server Forums

New to SQL Server Programming

Re format a fields values

Author

Topic

nuttreeuk
Starting Member

5 Posts

Posted - 2013-07-01 : 07:58:00

Hi all.

I have a database with a field that should contain a six digit number.

Unfortunately during the import process all the leading zeros were stripped off.

I need a simple sql script to reformat this single field to six digits but retain the data values, i.e 17 needs to be 000017
Server is SQL Server 2005.

I have tried:

SELECT SUBDESCRIPTION3,
REPLICATE ('0',6 - LEN(SUBDESCRIPTION3)) + CAST (SUBDESCRIPTION3 AS VARCHAR) AS SUBDESCRIPTION3
FROM ITEM;

But although this displays what I want it does not insert these values to the table.

Thank you.

Ed.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-01 : 08:15:35

To insert, i believe the datatype of the corresponding table's field/column would need to be set as Varchar

Cheers
MIK

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-01 : 08:17:53

The reason, most likely, is that the SUBSCRIPTION3 column is of numeric type rather than character type. Numeric types do not store formatted numbers - they store the representation of the number.

The recommended practice is to keep numeric values in columns of numeric data type and format it to your liking when you retrieve. Your query can be simplified a little bit as follows:

SELECT RIGHT('000000'+CAST(SUBSCRIPTION3 AS VARCHAR(32)),6)

If you do want to change the data type to a character type (and I strongly recommend against that), first see what the data type is:


SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'YourTableName' AND COLUMN_NAME = 'SUBSCRIPTION3'

Then, if you like change it:

ALTER TABLE YourTableName ALTER COLUMN SUBSCRIPTION3 VARCHAR(32)

That length of 32 is just arbitrary. You can pick another number if you like as long as it is wide enough.

nuttreeuk
Starting Member

5 Posts

Posted - 2013-07-01 : 08:20:04

The current field is set to nvarchar.

if i was to chage the above to match would that then insert the values as i want.

Thank you.
Ed.

nuttreeuk
Starting Member

5 Posts

Posted - 2013-07-01 : 08:23:45

Understood, but the field will accept the six number format if i update it manually.

Updating that way is not an option as there are over 17000 entries.

Thank you.
Ed.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-01 : 08:24:08

James has given you a good narrative of what and how to do .. so upto you to decide..

[quote]
The current field is set to nvarchar.

if i was to chage the above to match would that then insert the values as i want.
[quote]

Do you mean you're still not able to insert it? Can you post complete error message?

Cheers
MIK

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-01 : 08:24:57

If it is already set to NVARCHAR and assuming you are just using the select statement you had in your original post, that only selects the data from the table. You need to update it with the value. So:

UPDATE YourTable SET 
	SUBSCRIPTION3 = RIGHT('000000'+CAST(SUBSCRIPTION3 AS NVARCHAR(32)),6)

If the column is simply NVARCHAR, that is not a generally recommended practice. You should specify a max length for the NVARCHAR column, e.g.

ALTER TABLE YourTableName ALTER COLUMN SUBSCRIPTION3 NVARCHAR(32)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-01 : 08:26:34

quote:
Originally posted by nuttreeuk

Understood, but the field will accept the six number format if i update it manually.

Updating that way is not an option as there are over 17000 entries.

Thank you.
Ed.

Updating 17,000 entries should not be a huge operation. See the update statement I posted in my previous reply.

nuttreeuk
Starting Member

5 Posts

Posted - 2013-07-01 : 09:03:12

Thank you Guys.

Worked like a dream.

one more question if you would be so kind, I have another field which needs the same formating but on condition the value is less than 200000.

would i simply add a WHERE condition to the statement.

Thank you.

Ed.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-01 : 09:51:53

Yes, you can do that. The query will fail if there are any values that cannot be converted to a numeric column. SQL will do the implicit conversions, but you might as well cast it explicitly in the where clause

UPDATE YourTable SET 
	YourColumn = RIGHT('000000'+CAST(YourColumn AS NVARCHAR(32)),6)
WHERE
	CAST(YourColumn AS FLOAT) < 200000

nuttreeuk
Starting Member

5 Posts

Posted - 2013-07-01 : 10:55:43

Thank you James.

You are a gentleman.

Ed.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-01 : 12:52:07

You are very welcome; glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources