topic Re: Build SQL Query with special characters in Product Forum

Build SQL Query with special characters

ChandraAndhe1 — Wed, 07 Jul 2021 18:00:12 GMT

Hi All,

I am trying to build a string to use in Query to Execute in SQL Server Object. This is how I am building the Query text.
[QueryText] = [QueryText] & "'" &[LogData] & "',"

Problem: [LogData] has some special characters like ', this is making the query not execute as it is not formed properly. How can we build a string that can take special characters.

Thanks for the help.

Regards,

Chandra

Re: Build SQL Query with special characters

EmersonF — Wed, 07 Jul 2021 18:58:50 GMT

Hi, try it:
"[QueryText] = '"&[QueryText]&"' AND [QueryText] ="""&[LogData]&""""

Re: Build SQL Query with special characters

ChandraAndhe1 — Wed, 07 Jul 2021 19:07:52 GMT

Emerson,

Thanks for the answer :).

Problem I have is, I want to be able to add any text with either single or double quote or any special character. I don't want to limit the users sending me any characters.

Users may send me [LogData] = This is a tes't message that uses " quote in the middle

I want to be able to insert into the MSSQL db without changing the characters.

Alternative is to clean the String for single Quotes and insert., but that is not what I am looking yet.

Thanks for your time again..:)

Re: Build SQL Query with special characters

EmersonF — Wed, 07 Jul 2021 19:43:43 GMT

You can replace LogData like this Replace([LogData];"'";"''") So I can get something like that, it worked for me 😄

"[QueryText] = '"&[QueryText]&"' OR  [QueryText] = '"&Replace([LogData];"'";"''")&"'"

Filter:

Collection Initial:

Collection After Filter:

I took this article as a basis:
https://chartio.com/learn/sql-tips/single-double-quote-and-backticks-in-mysql-queries/