I need the query for sql server 2008 which remove the extra zeroes in the columns below is my table structure and values
CREATE TABLE[dbo].[tblZeroes]
(
iSrNum int PRIMARY KEY IDENTITY,
vNumbers varchar(20)
)
INSERT INTO [dbo].[tblZeroes] VALUES('00001')
INSERT INTO [dbo].[tblZeroes] VALUES('001.00')
INSERT INTO [dbo].[tblZeroes] VALUES('00001000')
INSERT INTO [dbo].[tblZeroes] VALUES('00.001')
INSERT INTO [dbo].[tblZeroes] VALUES('0.10')
Thanks
You can use below line of code
SELECT Removed_Zeroes=SUBSTRING(vNumbers, PATINDEX('%[^0 ]%', vNumbers + ' '), LEN(vNumbers))
FROM [dbo].[tblZeroes]
or
SELECT Removed_Zeroes=RIGHT(vNumbers, LEN(vNumbers+'a') -PATINDEX('%[^0 ]%', vNumbers + 'a' ))
FROM [dbo].[tblZeroes]
Note: ^0 =It means that Find the first position a character in a string which is not 0