0

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?

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.

For ex: I have these fields on a page:

Name ,  LastName , Address

And the entered values for above fields are:

Name : *-+-#.  Wwweee4426554456666yyyy5uuuuttrrrreree6655zfgh\\][;'/.uuuuuuuu66uuyt,+_)(*&^%$#@!~|}{:\\\"?><\\\\][;'/.,+_)(*&^%$#@!~|}{:\\\"?><\\\\][;'/.,+_)(*&^%$#@!~|}{:\\\"?><\\\\][;'/.,+_)(*&^%$#@!~|}{:\

LastName : Piterson
Address  : Park Road, LA

And I am showing the output like a JSON string below-

[{"Key":"Name","Value":"*-+-#.Wwweee4426554456666yyyy5uuuuttrrrreree6655zfgh\\][;'/.uuuuuuuu66uuyt,+_)(*&^%$#@!~|}{:\\\"?><\\\\][;'/.,+_)(*&^%$#@!~|}{:\\\"?><\\\\][;'/.,+_)(*&^%$#@!~|}{:\\\"?><\\\\][;'/.,+_)(*&^%$#@!~|}{:\"},{"Key":"LastName","Value":"Piterson"},{"Key":"Address","Value":"Park Road, LA"}]

But while parsing this string I am getting a parsing error below -

"After parsing a value an unexpected character was encountered: K. Path '[4].Value', line 1, position 1246."

I am using below SQL Server function to parse the string -

ALTER function [dbo].[fnEscapeString](@text nVARCHAR(MAX))
RETURNS NVARCHAR(MAX)  
as
BEGIN

    --if(CHARINDEX() )
    if (CHARINDEX('\',@text) > 0)
    set @text = Replace(@text,'\','\\')

    if (CHARINDEX('"',@text) > 0)
    set @text = Replace(@text,'"','\"')

    return @text

END

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.

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
GreenSoft
  • 41
  • 1
  • 4
  • 12
  • Which version of SQL-Server? – Shnugo Sep 06 '16 at 06:31
  • The given example is not much more than 300 characters. Your error message tells about *position 1246*. The function you provide seems to work well with the given string... Might be, that the problem occurs somewhere else... – Shnugo Sep 06 '16 at 06:37
  • @Shnugo, SQL Server 2012 – GreenSoft Sep 06 '16 at 06:40
  • @Shnugo, I just shared the piece of code for example the whole string is big. But the point is this string is not parsing in JSON format by my function. – GreenSoft Sep 06 '16 at 06:41
  • How do you build the JSON? The current string is **not** a valid JSON – Sir Rufo Sep 06 '16 at 06:54
  • @Sir Rufo, actually I am making a JSON string from SQL Server by parsing the strings in a function above. And building this JSON in C#. But as I mentioned my function is not able to parse this string in JSON format. – GreenSoft Sep 06 '16 at 06:58
  • Do you know, that SQL Server 2016 supports JSON with built in methods? Might be an option... In your question I read *But not working with above string*. This is not true... If you want help, you must provide a [MCVE](http://stackoverflow.com/help/mcve). The given information is not enough... – Shnugo Sep 06 '16 at 07:00
  • @GreenSoft So you want to **build** the JSON. **Parse** is just the opposite of that. – Sir Rufo Sep 06 '16 at 07:02

3 Answers3

1

You can directly convert your table data to json in 2016 for example,

SELECT name, surname  
FROM emp  
FOR JSON AUTO

but in case of lower versions you have to convert your sql table data to xml and then to Json.

Please refer this link to parse SQL Data to Json.

http://www.codeproject.com/Articles/815371/Data-Parsing-SQL-to-JSON

StackUser
  • 5,370
  • 2
  • 24
  • 44
0

You can try this as mentioned here
var my_JSON_object = !(/[^,:{}\[\]0-9.\-+Eaeflnr-u \n\r\t]/.test( text.replace(/"(\\.|[^"\\])*"/g, ''))) && eval('(' + text + ')');

Community
  • 1
  • 1
Mohammad Arshad Alam
  • 9,694
  • 6
  • 38
  • 61
-2

Try converting the input string to JSON by using:

a) System.Web.HttpUtility.JavaScriptStringEncode

string jsonEncoded = HttpUtility.JavaScriptStringEncode(s)

or

b) NuGet Package Newtonsoft.Json

string jsonEncoded = JsonConvert.ToString(s)

Reference: How to escape JSON string?

Community
  • 1
  • 1
sly
  • 300
  • 1
  • 5