REMOVE UNWANTED ZERO FORM COLUMN

Manoj Bhatt

Total Post:154

Points:1086
Posted by  Manoj Bhatt
 1573  View(s)
Ratings:
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
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: remove unwanted zero form column

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!