[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to remove '0' from left of the column in sql server 2005

Posted on 2008-11-18
5
Medium Priority
?
828 Views
Last Modified: 2012-05-05
colum type is varchar and want to remove '0' only from left.
sample data
000007
000008
000009
000010

output should be
7
8
9
10



0
Comment
Question by:easy_life
5 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 22986486
One easy way if these are numbers is to:

CAST(columnname AS INT)

You may want BIGINT depending on the scale of the field, but hopefully you get the idea.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 22986493
SELECT CAST (urColumn as INT)  newname
from urTable
0
 
LVL 4

Expert Comment

by:justin-clarke
ID: 22986538
SELECT (CAST Col1 AS int) AS Col1

Then converts it to an INT therefore removing the leading zeros
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 22987314
SELECT SUBSTRING(columnName, PATINDEX('%[^0]%', columnName), DATALENGTH(columnName))


For example:

SELECT SUBSTRING(columnName, PATINDEX('%[^0]%', columnName), DATALENGTH(columnName))
FROM (
    select '000007' as columnname union all select
'000008' union all select
'000009' union all select
'000010' union all select
'00010010'
) as testdata
0
 

Author Closing Comment

by:easy_life
ID: 31517919
Thanks for your quick response
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question