Building a text string containing a single quote.

Business Central/NAV Technical Forum
1

Can anyone tell me how to populate a text string variable with a value that actually contains the ’ (single quote). e.g. SQLSTR:=‘Call xxx/yyyy ‘LiteralValue’’; or e.g. SQLSTR:=‘Call xxx/yyyy ’ + ‘’’ + Variable + ‘’’; The syntax checker won’t allow/gets confused with usage of the single quote like these examples. (The remote system (iseries) that I am running the call to a stored procedure on demands the parameter value in single quotes.) Is there a way around the problem? Thanks!

2

search for : Problem with ’ in string and you wil find the answer. regards,

3

You are so close! It’s actually ‘’ that works as a single quote, so; SQLSTR := ‘Call xxx/yyyy ’ + ‘’’’ + Variable + ‘’’’; Should work for you.

4

Thank for your responses! Lessi: I get rather a lot of results and I can’t find the relevant ones! Can you point me to one, if you have found the one I am looking for? Thanks Again. John: This gives me string: Call xxx/yyyyy “Value”, assuming your example was single-double-single(?). This does work when querying a dbIV database for example, but I am querying DB2 the iSeries database. I get OS/400 SQL error SQL0104 - Token “Value” was not valid. This I guess is just a quirk of OS/400 SQL. But if I can just get single quotes around the value I know it’ll work!

5

Hello, For anyone who’s interested here is one way of getting single quotes in a string… Create a table that holds SQLstrings e.g. called StringTable Enabled Field No. Field Name Data Type Length Yes 1 SQLStringNumber Integer Yes 2 SQLString Text 250 Run from object designer and populate it manually with a record with text that is single quote - '. Get this record before you build your string. Build string in normal way adding (+) the Stringtable.SQLString. e.g. StringTable.GET(1); SQLStr :='CALL xxx/yyyy ’ + Stringtable.SQLString + Variable + SingleQuote.SQLString; and hey presto! SQLStr=Call xxx/yyyy ‘VariableValue’. Surely there is a more elegant solution though?

6

Hi. Why don’t you use this: Variable := ‘LiteralValue’; SQLSTR := ‘Call xxx/yyyy ’ + ‘’’’ + Variable + ‘’’’; The quotes around the variable Variable are 4 single quotes, not 1 single quote, a double quote and another single quote. This way, you get the string: Call xxx/yyyy ‘LiteralValue’ Regards

7

Hi McArthur, 1. Define a Text Constant(say TXT001) with ConstValue '. 2. SQLSTR := 'Call xxx/yyy ’ + TXT001 + Variable + TXT001.

8

Hi McArthur, 1. Define a Text Constant(say TXT001) with ConstValue '. 2. SQLSTR := 'Call xxx/yyy ’ + TXT001 + Variable + TXT001.