asked
Taking out leading zeros for xml path
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 00038881.00000070 i am trying to create: 38881.70 But instead get 38881.7
This site runs on votes. Please mark all helpful answers by clicking the thumbs up symbol next to those answers. Indicate any answer that lead to a solution by clicking on the check mark next to it.
It looks like you're off by one. Try changing the last '0' to '00' DECLARE @mattercode VARCHAR(17) = '00038881.00000070' select LTRIM(REPLACE(SUBSTRING(@mattercode, 1, CHARINDEX('.', @mattercode)), '0', '')) + LTRIM(REPLACE(SUBSTRING(@mattercode, CHARINDEX('.', @mattercode) + 1, LEN(@mattercode)), '00', '')) as 'MATTERCODE' I get `38881.70` with the second 0 and `38881.7` without. **[Edit]** Thanks for the sample data, that really helps. The way these are stored really complicates things when it's time to display them! I've updated the query so it leaves trailing zeroes intact and won't leave any leading zeroes (it still works with the .70 from the previous example) DECLARE @mattercode VARCHAR(17) = '00038881.00000004' select LTRIM(REPLACE(SUBSTRING(@mattercode, 1, CHARINDEX('.', @mattercode)), '0', '')) + case when SUBSTRING(@mattercode, charindex('.', @mattercode) + 1, len(@mattercode)) = '00000000' then '' else substring(SUBSTRING(@mattercode, charindex('.', @mattercode) + 1, len(@mattercode)), patindex('%[1-9]%', SUBSTRING(@mattercode, charindex('.', @mattercode) + 1, len(@mattercode)) ), len(@mattercode))end **[Final Edit]** (I would hope) Now that we have the number *after* the "." working, I noticed that the code to remove "leading zeroes" from the number before the "." is actually removing all of the zeroes before the decimal point, not just the leading ones. If you only want to remove leading zeroes, you're going to have to do some extreme parsing again (an extension to ltrim() that would let you specify a character to trim would be helpful here). Here is it with zeroes embedded in the leading number: DECLARE @mattercode VARCHAR(17) = '00078040.00000001' select case when LEFT(@mattercode, charindex('.', @mattercode)) = '00000000.' then '' else SUBSTRING(LEFT(@mattercode, charindex('.', @mattercode)), PATINDEX('%[1-9]%', LEFT(@mattercode, charindex('.', @mattercode))), charindex('.', @mattercode) - patindex('%[1-9]%', @mattercode)+1)end + case when SUBSTRING(@mattercode, charindex('.', @mattercode) + 1, len(@mattercode)) = '00000000' then '' else substring(SUBSTRING(@mattercode, charindex('.', @mattercode) + 1, len(@mattercode)), patindex('%[1-9]%', SUBSTRING(@mattercode, charindex('.', @mattercode) + 1, len(@mattercode)) ), len(@mattercode))end
Thank you. Here is a sample matter codes to get leading zero out at beginning off first set of numbers and after decimal point. 00081197.00000001 00086235.00000002 00086247.00000001 00091532.00000001 00075570.00000001 00081596.00000001 00081597.00000002 00086714.00000001 00086721.00000001 00086679.00000001 00086684.00000001 00086685.00000001 00058302.00000001 00076357.00000001 00076384.00000001 00092359.00000001 00092366.00000001 00092367.00000001 00064165.00000001 00058349.00000001 00058350.00000003 00092382.00000001 00070343.00000001 00087631.00000001 00087632.00000001 00087643.00000001 00050707.00000001 00070844.00000001 00082809.00000001 00083049.00000001 00083063.00000001 00088417.00000001 00088418.00000001 00078120.00000001 00078121.00000003 00078121.00000001 00078125.00000001 00078144.00000001 00078040.00000001 00078043.00000001 00083443.00000003 00083444.00000001 00078576.00000001 00078586.00000001 00083878.00000001 00089332.00000001 00089333.00000001 00072466.00000001 00084415.00000001 00061212.00000001 00080022.00000001 00080024.00000001 00085276.00000001 00077724.00000001 00077708.00000001 00077712.00000001 00083094.00000001 00086299.00000002 00063285.00000001 00068225.00000003 00091964.00000001 00091976.00000001 00091977.00000001 00091978.00000001 00091980.00000001 00091981.00000001 00091984.00000001 00091985.00000001 00091986.00000001 00091988.00000001 00091990.00000001 00092002.00000001 00081958.00000002 00081958.00000001 00081962.00000001 00081963.00000001 00067356.00000002 00080774.00000001 00067854.00000001 00045872.00000001 00075551.00000001 00081572.00000001 00054103.00000001 00092004.00000001 00092006.00000002 00081982.00000001 00087195.00000001 00087196.00000001 00064204.00000001 00064665.00000001 00064667.00000002 00082830.00000001 00088156.00000001 00071259.00000001 00071262.00000001 00083099.00000001
Hi KenJ I am doing some integration work from our existing system to new as an output file. I am passing the reference number without the leading zeros to the new system, to allow the users to find the case without inserting all the leading zeros. The reference number are also merged into forms and documents minus the leading zeros. I have managed to find a function that works on the new system (my other thread on this site). But it seems to break.
If those codes are always numeric and fixed in that format, then this does the job select cast(part1 as varchar(8))+'.'+cast(part2 as varchar(8)) as result from ( values('00038881.00000070'),('00081197.00000001'),('00086235.00000002') ) data(MATTERCODE) cross apply ( select cast(MATTERCODE as decimal(8,0)) as part1, cast(STUFF(MATTERCODE,1,9,'') as decimal(8,0)) as part2 )x
Thanks KenJ, but only if that format is fixed as two 8 digit numerics with a decimal separator....
17 People are following this question.
