Deleting 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

SQL Server 2000 Forums

Transact-SQL (2000)

Deleting leading zero

Author

Topic

acwong
Starting Member

2 Posts

Posted - 2006-07-13 : 15:38:05

A table has a field with entries such as 0123, 0456, 023, 042.
I need to remove the leading zero in all entries.
Using "where textid like '0__'" I can select all 0?? entries.
But I have not been able to remove.
Tried various update statements but no luck.
Database is on a SQL 2k server.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-13 : 15:43:12

The below will work if there is only one leading zero. Let us know if there is ever more than one.

UPDATE YourTable
SET YourColumn = SUBSTRING(YourColumn, 2, DATALENGTH(YourColumn))
WHERE YourColumn LIKE '0%'

Tara Kizer
aka tduggan

acwong
Starting Member

2 Posts

Posted - 2006-07-14 : 14:23:02

Tara,

Thank you, the script worked.

Alex

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources