delete 0 from cols - 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

delete 0 from cols

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-02-23 : 10:30:47

hi team , i have table

abc with column :id and following values are inserted in it

001
002
0003
0004
00444
001401

i need to truncate all the 0 before digits , i need not to delete 0 between two non
zero digits

challenge everything

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 10:40:16

UPDATE dbo.Table1
SET Col1 = CAST(Col1 AS BIGINT)

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 14:50:01

i hope you dont have any non numeric data in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-02-23 : 22:46:33

please suggest, if there is any non numeric data

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-23 : 22:57:44

[code]substring(val, patindex('%[^0]%', val), len(val))[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 23:25:20

quote:
Originally posted by pnpsql

please suggest, if there is any non numeric data

challenge everything

hmm.. are you asking a way to find those?

just do like


SELECT *
FROM table
WHERE Field LIKE '%[^0-9]%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-02-26 : 23:19:13

found some non numeric values in the column . please help .

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-26 : 23:21:58

quote:
Originally posted by pnpsql

found some non numeric values in the column . please help .

challenge everything

what do you want to do with such data ?

post some example and expected result

KH
[spoiler]Time is always against us[/spoiler]

Jayam.cnu
Starting Member

45 Posts

Posted - 2012-02-27 : 02:31:34

than why dont you alter this column into Numeric datatype...

alter table <tablename> alter column <columname> Numberic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources