title: SQL XML parsing split the string on letters 'TH'
body: <p>I want to split a string into multiple values based on a special symbol. e.g., Here is the string</p>
  
  <blockquote>
    <p>JdwnrhþTHIMPHUþOTHþþ10/1991þ02/02/2011þBHUTAN</p>
  </blockquote>
  
  <p>I want it to be:</p>
  
  <blockquote>
    <p>Jdwnrh THIMPHU OTH  10/1991 02/02/2011 BHUTAN</p>
  </blockquote>
  
  <p>I am using the following SQL:</p>
  
  <pre><code>DECLARE @delimiter VARCHAR(50)
  SET @delimiter='þ' 
  ;WITH CTE AS
  ( 
      SELECT
          CAST('&lt;M&gt;' + REPLACE(REPLACE(CAST(DATA as nvarchar(MAX)), @delimiter , '&lt;/M&gt;&lt;M&gt;'), '&amp;', '&amp;amp;') + '&lt;/M&gt;' AS XML) 
          AS BDWCREGPREVADDR_XML
      FROM [JACS_RAVEN_DATA_OLD].dbo.BDWCREGPREVADDR
  )
  
  SELECT
       BDWCREGPREVADDR_XML.value('/M[1]', 'varchar(50)') As streetNo,
       BDWCREGPREVADDR_XML.value('/M[2]', 'varchar(50)') As suburb,
       BDWCREGPREVADDR_XML.value('/M[3]', 'varchar(3)') As stateCode,
       BDWCREGPREVADDR_XML.value('/M[4]', 'varchar(10)') As postalCode,
       BDWCREGPREVADDR_XML.value('/M[7]', 'varchar(50)') As country,
       BDWCREGPREVADDR_XML.value('/M[5]', 'varchar(50)') As dateFrom,
       BDWCREGPREVADDR_XML.value('/M[6]', 'varchar(50)') As dateTo
  FROM CTE
  GO
  </code></pre>
  
  <p>The query works well on all the strings other than the one provided as an example. For above the string, the query returns the following:</p>
  
  <blockquote>
    <p>'Jdwnrh' ' ' 'IMPHU' 'O' ' ' '10/1991' '02/02/2011' 'BHUTAN'</p>
  </blockquote>
  
  <p>It seems the code takes letters 'TH' as a new attribute and split the string on it. Does anyone know how to resolve this issue?</p>