Leading Zero - 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

Leading Zero

Author

Topic

juliahunt428
Starting Member

3 Posts

Posted - 2010-11-04 : 11:35:01

I need to take a column of id numbers and update them to add a leading zero. I need to do this directly into the database using an update statement.

TableA contains a column "memberID". It is varchar (16) type. The data contained in it is not always the same number of characters. All I want to do is add a zero in front of the first digit.

Before:
TableA.memberid = 123456
After
TableA.memberid = 0123456

I realize this is probably really simple, but I have never done this before and am relatively new to SQL.

Thank you.

Julia C. Hunt

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 11:40:08

update tableA set memberid = '0'+memberid
where left(memberid,1) <> '0'

No, you're never too old to Yak'n'Roll if you're too young to die.

juliahunt428
Starting Member

3 Posts

Posted - 2010-11-04 : 14:51:12

Hey webfred,
I tried this and it would not work on the ids that already began with a zero.
Any ideas?
Thanks!
Julia

Julia C. Hunt

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 15:05:06

Yes I thought it shouldn't do that for ids that already began with a zero.
To do it for all just drop the where clause.

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-11-04 : 15:28:18

declare @str varchar(50)

set @str = '0123456'

select '0'+convert(varchar(50),convert(int,@str) )

Jim

Everyday I learn something that somebody else already knew

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-04 : 17:49:58

Have noticed that most identifers are fixed length strings? Just make the membership_id a fully padded CHAR(16) and be safe.

UPDATE Membership
SET member_id
= REVERSE (SUBSTRING (REVERSE(@member_id) + '0000000000000000', 1, 16));

Finally ALTER the table so the column is constrained:

member_id CHAR(16) NOT NULL
CHECK (Member_id NOT LIKE (%[^0-9]%)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2010-11-05 : 10:18:45

This will pad zeros to seven characters:


UPDATE Membership
SET member_id = right('0000000' + member_id, 7)
where len(member_id) < 7

Be sure you include the WHERE clause, or you risk truncating existing data that contains more than seven characters.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

juliahunt428
Starting Member

3 Posts

Posted - 2010-11-05 : 10:40:29

Thanks for all of your replies everyone - this is all good stuff!
Take care,
Julia

Julia C. Hunt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources