quote:

---

Originally posted by sqlfresher2k7

I need a query to add 4 zeros before peroid for the field values.
Ex:
2.16.840.1.114222
2.16.840.1.114222.65.61


Exepcted output

00002.000016.0000840.00001.0000114222
00002.000016.0000840.00001.0000114222.000065.000061





Thanks for your help in advance.

SELECT REPLACE( '0000' + '2.16.840.1.114222' , '.', '.0000')

---

declare @sqlfresh table(id int identity(1,1), value varchar(max))
INSERT INTO @sqlfresh
values('2.16.840.1.114222')
INSERT INTO @sqlfresh
values('2.16.840.1.114222.65.61')

SELECT sq.id, Tags.val.value('.', 'VARCHAR(MAX)') AS policy#,
       CASE 
          WHEN LEN(Tags.val.value('.', 'VARCHAR(MAX)')) > 4 THen  Tags.val.value('.', 'VARCHAR(MAX)')
          ELSE REPLACE(POWER(10, 5 - LEN(Tags.val.value('.', 'VARCHAR(MAX)'))  ), '1','') + Tags.val.value('.', 'VARCHAR(MAX)')
       END as yougi
       
    --, '0000' + REPLACE(value,'.','.0000')
  FROM(  
		  SELECT id, CAST('<t>' + REPLACE(value, '.', '</t><t>') + '</t>' AS XML) AS TAG  
			FROM @sqlfresh
	  ) TAB 
 CROSS APPLY TAG.nodes('/t') as Tags(val)
 inner join @sqlfresh sq
 on sq.id = TAB.id 
 

;with cteJunk(id, policy#, yougi, value)			
AS
(
SELECT sq.id, Tags.val.value('.', 'VARCHAR(MAX)') AS policy#,
       CASE 
          WHEN LEN(Tags.val.value('.', 'VARCHAR(MAX)')) > 4 THen  Tags.val.value('.', 'VARCHAR(MAX)')
          ELSE REPLACE(POWER(10, 5 - LEN(Tags.val.value('.', 'VARCHAR(MAX)'))  ), '1','') + Tags.val.value('.', 'VARCHAR(MAX)')
       END as yougi, value
  FROM(  
		  SELECT id, CAST('<t>' + REPLACE(value, '.', '</t><t>') + '</t>' AS XML) AS TAG  
			FROM @sqlfresh
	  ) TAB 
 CROSS APPLY TAG.nodes('/t') as Tags(val)
 inner join @sqlfresh sq
 on sq.id = TAB.id 
 ) 
 
 
SELECT
   t1.id,
   value was ,
   IsNow = substring((SELECT ( '.' + yougi )
                           FROM cteJunk t2
                           WHERE t1.id = t2.id
                           ORDER BY id
                           FOR XML PATH( '' )
                          ), 2, 1000 
                         )FROM cteJunk t1
GROUP BY id, value