How can I Remove the Leading Zeros in SQL?

[PsYcHo 1]

Hello friends,
I have recently started with the SQL. I have done little use of SQL as the backend of my assignment. But I am getting frustrated because I am unable to remove the starting zeroes. I have tried lot of different statements but that was not so useful. Can anyone out there tell me how can I Remove the Leading Zeros in SQL..?? Please help me soon as possible..!!

[Reegan]

I have provided you with the code that is used for removing the leading zeroes from a particular field. Lets say for an example, if the field contains '0005BC', the select statement needs to return the data as '5BC'. I think that you were asking the same thing. ust look the following code which does the same :

select substring(ColumnName, patindex('%[^0]%',ColumnName), 10)

Hope that it will help you.!!

[Praetor]

I would like to ask you that will your data always need to be stripped of the leading zeros? If you want to stripped of the leading zeros always, then it would be better to fix the data in the table and when it is entered or imported than to run a function in every select. If you are using for some cases and some situations, then using the code would be good. And if you want to remove the leading zeroes every time, then I would like to suggest you to make the changes in the database, instead of using the code everytime.

[Modifier]

If you are having the column called productno which consists of values which are having the leading zeroes like "000123, 000124, 000125,...etc". Then you can remove the leading zeroes from the column by using the code that i have mentioned below :

select productno, cast(productno as bigint), cast(productno as int)
from <YourTableNameHere>

by doing this the leading zeroes will get removed.

[void]

I am providing you with the code which is used for updating statement to remove leading zeros from a column. I have assumed that the column is a varchar field. This code will work even the number of the zeroes varies. Means if one variable is having 2 zeroes and one variable is having 3 zeroes then also this code will work properly.

UPDATE table
SET column=LTRIM(column,'0');

Hope that you find this code useful.