Home > DeveloperSection > Forums > remove unwanted zero form column
Manoj Bhatt
Manoj Bhatt

Total Post:153

Points:1079
Posted on    January-05-2013 4:41 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 1276  View(s)
Rate this:
Hi Guys,

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


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    January-05-2013 7:03 AM

Hi Manoj Bhatt

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

Don't want to miss updates? Please click the below button!

Follow MindStick