locked
Taking out leading zeros for xml path RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-912404483 posted
    Hi, need some help please

    For XML Path, i tried to use:

    select top 1 LTRIM(REPLACE(SUBSTRING(mm.MATTERCODE, 1, CHARINDEX(''.'', mm.MATTERCODE)), ''0'', '''')) +
                         LTRIM(REPLACE(SUBSTRING(mm.MATTERCODE, CHARINDEX(''.'', mm.MATTERCODE) + 1, LEN(mm.MATTERCODE)), ''0'', '''')) as ''MATTERCODE''

    From <FULL_MATTER_CODE>00038881.00000070</FULL_MATTER_CODE> i am trying to create:

    <MATTERCODE>38881.70</MATTERCODE>

    But instead get <MATTERCODE>38881.7</MATTERCODE>
    Tuesday, October 15, 2013 4:55 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-933407369 posted

    hi deighton,

    According to your description, i understand you want to convert '00038881.00000070' to '38881.70'. First you can convert the data to int the

    I would suggest you try to use CAST key word to convert the datatype, seems like below:

    select top 1  CAST(CAST(  LTRIM(
          SUBSTRING(MATTERCODE, 1, CHARINDEX('.', MATTERCODE)-1)) AS int)AS NVARCHAR(25))+'.'
		  +
         CAST(CAST( LTRIM(SUBSTRING(MATTERCODE, CHARINDEX('.', MATTERCODE) + 1, LEN(MATTERCODE))) AS int)AS NVARCHAR(25)) as MATTERCODE
 From testtable mm;

    I hope it helps you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 16, 2013 4:13 AM