Remove numeric characters from xml data with data type nvarchar(max)

All replies

• 

  

  0

  Sign in to vote

  DECLARE @xml AS xml
  SET @xml='<Hospital>
  <Assessment>
  <productCode>Cas234p</productCode>
  </Assessment>
  </Hospital>'
  
  SELECT CAST(dbo.ufn_onlychar (CAST(@xml AS NVARCHAR(MAX))) AS XML)
  
  
  ---SELECT PATINDEX('%[0-9]%',CAST(@xml AS NVARCHAR(MAX))) AS c
  
  CREATE FUNCTION dbo.ufn_onlychar (@StrVal AS VARCHAR(8000))
  RETURNS VARCHAR(8000)
  AS
  BEGIN
    WHILE PATINDEX('%[^a-z]%', @StrVal) > 0
      SET @StrVal = REPLACE(@StrVal,
          SUBSTRING(@StrVal,PATINDEX('%[^a-z]%', @StrVal),1),'')
    RETURN @StrVal
  END
  GO
  

  ---

  Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
  
  MS SQL optimization: MS SQL Development and Optimization
  MS SQL Consulting: Large scale of database and data cleansing
  Remote DBA Services: Improves MS SQL Database Performance
  SQL Server Integration Services: Business Intelligence
  

  Monday, May 19, 2014 6:44 AM
• 

  

  1

  Sign in to vote

  You can also do it inline within xml

  declare @t table
  (
  x xml
  )
  insert @t
  values('<Hospital><Assessment><productCode>Cas234p</productCode></Assessment></Hospital>')
  
  
  
  
  UPDATE t
  SET x.modify('replace value of ((/Hospital/Assessment/productCode/text())[1]) with (sql:column("ModifiedCol"))')
  FROM
  (
  SELECT STUFF(m.n.value('productCode[1]','varchar(100)'),PATINDEX('%[A-za-z][0-9]%',m.n.value('productCode[1]','varchar(100)'))+1,PATINDEX('%[0-9][A-za-z]%',m.n.value('productCode[1]','varchar(100)'))-PATINDEX('%[A-za-z][0-9]%',m.n.value('productCode[1]','varchar(100)')),'') AS ModifiedCol, x
  FROM @t
  CROSS APPLY x.nodes('/Hospital/Assessment')m(n)
  )t
  
  SELECT * from @t

  
  

  ---

  Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

  Monday, May 19, 2014 7:38 AM
• 

  

  0

  Sign in to vote

  Thank you very much for your reply.

  But I need to update the table as Casp or other value only for product code. There are other values before after the productCode which also has numeric values. Any help would be appreciated.

  SET @xml='<Hospital>

  < Assessment>

  <Type>3</Type>

  < productCode>Cas234p</productCode>

  <AssessmentCode>13434</AssessmentCode>

  < /Assessment>
  < /Hospital>'

  ---

  -kccrga http://dbatrend.blogspot.com.au/

  Monday, May 19, 2014 7:40 AM
• 

  

  0

  Sign in to vote

  Thank you very much for your reply.

  But I need to update the table as Casp or other value only for product code. There are other values before after the productCode which also has numeric values. Any help would be appreciated.

  SET @xml='<Hospital>

  < Assessment>

  <Type>3</Type>

  < productCode>Cas234p</productCode>

  <AssessmentCode>13434</AssessmentCode>

  < /Assessment>
  < /Hospital>'

  ---

  -kccrga http://dbatrend.blogspot.com.au/

  Sorry did you mean you need to remain other values as is. If yes, then posted script work fine. Or do you want to replace them as well?

  ---

  Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

  Monday, May 19, 2014 8:04 AM
• 

  

  0

  Sign in to vote

  Thanks for your reply. I meant the other values should stay as it is.

  I have namespace which is making it difficult for me to replace.
  <tns:ClaimResponse xmlns:tns="http://test/online/read/version-2">

  ---

  -kccrga http://dbatrend.blogspot.com.au/

  Monday, May 19, 2014 11:36 PM
• 

  

  0

  Sign in to vote

  I have fixed namespace by using the below query.

  declare @t table(x xml)insert @tvalues('<Hospital><Assessment><productCode>Cas234p</productCode></Assessment></Hospital>') 
  UPDATE tSET x.modify('replace value of ((/*:/Hospital/Assessment/productCode/text())[1]) with (sql:column("ModifiedCol"))')
  FROM(SELECT STUFF(m.n.value('productCode[1]','varchar(100)'),PATINDEX('%[A-za-z][0-9]%',
  m.n.value('productCode[1]','varchar(100)'))+1,PATINDEX('%[0-9][A-za-z]%',m.n.value('productCode[1]','varchar(100)'))-PATINDEX('%[A-za-z][0-9]%',m.n.value('productCode[1]','varchar(100)')),'') AS ModifiedCol, xFROM @tCROSS APPLY x.nodes('/*:/Hospital/Assessment')m(n))tSELECT * from @t

  
  

  ---

  -kccrga http://dbatrend.blogspot.com.au/

  • Marked as answer by Mike Yin Wednesday, May 28, 2014 3:52 PM

  Friday, May 23, 2014 1:16 AM
• 

  

  1

  Sign in to vote

  I have fixed namespace by using the below query.

  declare @t table(x xml)insert @tvalues('<Hospital><Assessment><productCode>Cas234p</productCode></Assessment></Hospital>') 
  UPDATE tSET x.modify('replace value of ((/*:/Hospital/Assessment/productCode/text())[1]) with (sql:column("ModifiedCol"))')
  FROM(SELECT STUFF(m.n.value('productCode[1]','varchar(100)'),PATINDEX('%[A-za-z][0-9]%',
  m.n.value('productCode[1]','varchar(100)'))+1,PATINDEX('%[0-9][A-za-z]%',m.n.value('productCode[1]','varchar(100)'))-PATINDEX('%[A-za-z][0-9]%',m.n.value('productCode[1]','varchar(100)')),'') AS ModifiedCol, xFROM @tCROSS APPLY x.nodes('/*:/Hospital/Assessment')m(n))tSELECT * from @t

  
  

  ---

  -kccrga http://dbatrend.blogspot.com.au/

  
  Hi Kccrga, do you mean the issue has been resolved?

  ---

  Regards, Leo

  Friday, May 23, 2014 1:00 PM
• 

  

  0

  Sign in to vote

  Yes it is resolved.

  ---

  -kccrga http://dbatrend.blogspot.com.au/

  Tuesday, May 27, 2014 7:13 AM