easy_life
asked on
how to remove '0' from left of the column in sql server 2005
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
sample data
000007
000008
000009
000010
output should be
7
8
9
10
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT (CAST Col1 AS int) AS Col1
Then converts it to an INT therefore removing the leading zeros
Then converts it to an INT therefore removing the leading zeros
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
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
ASKER
Thanks for your quick response
from urTable