String manipulation that includes dbl quotes

[elcaro2k]

What is the best way to do string manipulations when the string has double quotes. Say if I want to change a SQL where clause into a report header:

(("Health Code" = 'Asthma') AND ("Disease Severity"='Severe))...

into

Health Code = Asthma
Disease Severity = Severe
...

The problem is that the string functions in both js and asp cannot handle the double quotes. Any ideas???

Thanks!

[beetle]

Here's the proper way to include quotes...

(("\"Health Code\"" = 'Asthma') && ("\"Disease Severity\""='Severe))

What exactly are you trying to do here? Are you stuffing the SQL into a javascript var and then want ot modify like above?

[elcaro2k]

I am getting the where clause from cognos and using it to create a table. But I need to take that same string and create a header for that summary table. I cannot control the way cognos sends the parameter.

[beetle]

var sqlStr = "((\"Health Code\" = 'Asthma') AND (\"Disease Severity\" = 'Severe))";
var outputHTML = sqlStr.replace(/['"\(\)]/g,'');
var outputHTML = outputHTML.replace("AND","<br>");
document.write(outputHTML);

[elcaro2k]

OK, but how do I get the forward slashes in the string?

[beetle]

You mean the backslashes? Well, they're not really there. They are just telling the javascript engine to ignore those as the literal separators, and store them as a character in the string. If your SQL output contains double quotes and you stuff it into a variable, then all should be ok.

Tell me where the string (("Health Code" = 'Asthma') AND ("Disease Severity"='Severe)) is coming from anyhow, because that doesn't look like it comes straight from SQL. I'm more accustomed to seeing something like this$sql = "SELECT * FROM table WHERE Health_Code='Asthma' AND Disease_Severity='Severe'";

[elcaro2k]

When I get to a point in a Cognos cube, I can do a drill through which sends the query string as part of the URL. I tell Cognos the web page to open but that is the only control I have until it gets to the page. When my page opens I extract the string from the URL by using:

unescape(Request.Form("filter")

which gives a string typical of the one below:

(("Incurred As Of Date" BETWEEN DATE '2001-06-01' AND DATE '2001-06-30') AND ("Health Mgmt Disease Name"='Asthma') AND ("Disease Severity Name"='Mild') AND ("Age Group Name"='5 to 9 years old' AND "Age Group Category Name"='Children') AND ("Gender Code"='MALE') AND ("Region Name" LIKE 'CENTRAL MO OTHER' AND "Region Category Name"='Central MO'))

[beetle]

You will have to know how to store that value into a javascript variable. In PHP, it looks something like this<script>
var sqlStr = "<?=$sql?>";
</script> It looks like Cognos uses VBScript ASP, which I don't know, but it should look relatively similar<%
Dim sqlVar = unescape(Request.Form("filter"); 'Now we have it stored
%>

<script>
var sqlSTr = "<%= sqlVar %>";
</script>ASP guys, feel free to make my hack attempt at writing ASP correct (if it needs it;))

[elcaro2k]

OK, I have it in a js var, now what?

[beetle]

Check my 2nd post in this thread :D

[elcaro2k]

I've got it all worked out! Thanks so much beetle!!!
:thumbsup:

[beetle]

Great. I should mention, however, that if I were doing this in PHP, I'd do the regular expression replacing with PHP rather than javascript and just send the formatted text to the page.

You may or may not have those controls available to you, since I don't really know what Cognos is....