title: How to parse a string in json format in SQL Server
body: <p>Is there a way/function/reg-ex in SQL Server or Visual Studio by which we can escape any character/special character within a string?</p>
  
  <p>I have a functionality/page where there are server text field and user can enter any kind of string there (including special characters). And as a result I am showing a JSON string as a 'Key', 'Value' pare of those text fields entries.</p>
  
  <p>For ex: I have these fields on a page:</p>
  
  <pre><code>Name ,  LastName , Address
  </code></pre>
  
  <p>And the entered values for above fields are:</p>
  
  <pre><code>Name : *-+-#.  Wwweee4426554456666yyyy5uuuuttrrrreree6655zfgh\\][;'/.uuuuuuuu66uuyt,+_)(*&amp;^%$#@!~|}{:\\\"?&gt;&lt;\\\\][;'/.,+_)(*&amp;^%$#@!~|}{:\\\"?&gt;&lt;\\\\][;'/.,+_)(*&amp;^%$#@!~|}{:\\\"?&gt;&lt;\\\\][;'/.,+_)(*&amp;^%$#@!~|}{:\
  
  LastName : Piterson
  Address  : Park Road, LA
  </code></pre>
  
  <p>And I am showing the output like a JSON string below-</p>
  
  <pre><code>[{"Key":"Name","Value":"*-+-#.Wwweee4426554456666yyyy5uuuuttrrrreree6655zfgh\\][;'/.uuuuuuuu66uuyt,+_)(*&amp;^%$#@!~|}{:\\\"?&gt;&lt;\\\\][;'/.,+_)(*&amp;^%$#@!~|}{:\\\"?&gt;&lt;\\\\][;'/.,+_)(*&amp;^%$#@!~|}{:\\\"?&gt;&lt;\\\\][;'/.,+_)(*&amp;^%$#@!~|}{:\"},{"Key":"LastName","Value":"Piterson"},{"Key":"Address","Value":"Park Road, LA"}]
  </code></pre>
  
  <p>But while parsing this string I am getting a parsing error below -</p>
  
  <pre><code>"After parsing a value an unexpected character was encountered: K. Path '[4].Value', line 1, position 1246."
  </code></pre>
  
  <p>I am using below SQL Server function to parse the string -</p>
  
  <pre><code>ALTER function [dbo].[fnEscapeString](@text nVARCHAR(MAX))
  RETURNS NVARCHAR(MAX)  
  as
  BEGIN
  
      --if(CHARINDEX() )
      if (CHARINDEX('\',@text) &gt; 0)
      set @text = Replace(@text,'\','\\')
  
      if (CHARINDEX('"',@text) &gt; 0)
      set @text = Replace(@text,'"','\"')
  
      return @text
  
  END
  </code></pre>
  
  <p>This function is working in many other cases (with many other strings). But not working with above string. I think this function is not enough able to parse all kind of strings.</p>
  
  <p>So is there any way where we can parse a string in a valid JSON row format. May be any reg-ex or sql function can do that. Please suggest.</p>