How do I add numbers to too all numbers in a field

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)

How do I add numbers to too all numbers in a field

Author

Topic

leodesol
Starting Member

9 Posts

Posted - 2007-03-08 : 11:53:47

I have a varchar field field. Some of the values of data in the field are 5,6, and 8 digit numbers, some are not.

Is there a SQL statement I can use to change only the 5 and 6 digit numbers to 8 digit numbers by adding 0s to the front of them?

Example:

12345 would become 00012345
and 123456 would become 00123456.

I also realize I might have to do this in 2 statements, and that would be fine as well. The tricky part of it is that there is also some data that is not numbers at all that I want to leave alone.

spejbl
Starting Member

28 Posts

Posted - 2007-03-08 : 12:24:20

this trick is your friend:

CASE WHEN ISNUMERIC(YourColumn)=1
  THEN RIGHT('00000000' + YourColumn, 8)
  ELSE YourColumn
END

but note:

ASPFAQ: What is wrong with ISNUMERIC()?
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

--
kb
http://kbupdate.info/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-03-08 : 22:15:22

As previously pointed out, ISNUMERIC should not be used as a substitute for IS ALL DIGITS... use this instead


 UPDATE yourtable
    SET yourcolumn = REPLACE(STR(yourcolumn,8),' ','0')
  WHERE yourcolumn NOT LIKE '%[^0-9]%

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources