I have an html page that has a list box of employee names. You can then select one or many of those names and click submit. When the user clicks submit, we take the values from that list box, massage it a bit, and then run a sql query to bring back information for those employees. We are having a problem with employees that have an apostrophe in their name. For example, Thomas O’Toole.

Because we allow for a multiple selection of names, I have to massage the data a bit to replace the OR’s with commas so they can be used in my sql query with an IN clause.
Once that has been done, I build a character string that is put into my sql query. I’m creating dynamic WHERE clauses for the sql so I can have 1 query with variables in it for whatever filter boxes were changed when the html page ran.
So here is what my code looks like:
-SET &FULL_LEADER_NAME = STRREP(2000,&LEADER_NAME,4,' OR ',3,' , ',990,A2000);
-SET &LEADER_LINE = 'AND ' | 'rtrim(D3.CRNT_LST_NM)' | ' || ' | ''', '' ' | '|| ' | 'rtrim(D3.CRNT_FRST_NM)' || ' IN( ' | '' || &FULL_LEADER_NAME || ')';
Then in my query, I have &LEADER_LINE as a variable. Obviously, there is quite a bit more to the story, but this is just a simple explanation.

When the query runs, it’s failing because the line in the query looks like this:
AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O'Toole, Thomas')
The sql can’t run with those 3 apostrophes like that.

My thinking was I would add another STRREP command to change all apostrophes to double apostrophes. That would fix the issue and my sql will run that way. I used this:
-SET &FULL_LEADER_NAME2 = STRRE0P(2000,&FULL_LEADER_NAME,1,'''',2,'''''',990,A2000);

And that worked except now all of my apostrophes are double apostrophes. So I get this result if I were to select just Thomas O’Toole:
AND rtrim(D3.CRNT_FRST_NM) IN(''O''Toole, Thomas'’)
and this if I were to select Thomas plus one more person:
AND rtrim(D3.CRNT_FRST_NM) IN(''O''Toole, Thomas'' , ''Osby, John'')

So my STREPP works but it adds changes to the double apostrophes that surround the name, not just the ones inside of it.

Has anyone ever dealt with this issue? I’m not sure how to do any commands that will allow me to change apostrophes in the middle of the name but not the ones on the outside of the name. Nor can I figure out a way to write any commands to remove those double apostrophes in the cases that I don’t want them.

Any ideas would be great. Thanks!

This message has been edited. Last edited by: FP Mod Chuck,

Try using &FULL_LEADER_NAME.QUOTEDSTRING

T

I tried to add the quotedstring to the following line:
-SET &FULL_LEADER_NAME2 = STRREP(2000,&FULL_LEADER_NAME.QUOTEDSTRING,1,'''',2,'''''',990,A2000);

That didn't make a difference.

You could try something like this:

  
-SET &LEADER_LINE = '''O''Toole, Thomas'' OR ''Osby, John'' OR ''Hunter, De''Andre''';

-TYPE Start LEADER_LINE  = &LEADER_LINE

-SET &LEADER_LINE = SUBSTRING(&LEADER_LINE, 2, &LEADER_LINE.LENGTH-2);

-SET &LEADER_LINE = STRREP(2000, &LEADER_LINE, 6, ''' OR ''', 1, '|', 2000, 'A2000');

-SET &LEADER_LINE = STRREP(2000, &LEADER_LINE, 1, '''', 2, '''''', 2000, 'A2000');

-SET &LEADER_LINE = '''' | STRREP(2000, &LEADER_LINE, 1, '|', 3, ''',''', 2000, 'A2000') || '''';

-SET &LEADER_LINE = TRUNCATE(&LEADER_LINE);

-TYPE Ending LEADER_LINE = &LEADER_LINE

Hi AprilC,
Unfortunately I have also run into this issue with our branch names. We have one with an apostrophe in it as well, and it does tend to throw us issues when we have it in a drop down selector box.
The only way I was able to get around this was to have a multi-select WHERE clause which defined out all the individual branch name fields. I am assuming this won't be as easy on your end however, since you more than likely have a lot more names in your DB than I do branches.

Just for example though, this is how I dealt with the ' in my code.

Branch Name: DOWNTOWN COEUR D'ALENE

WHERE J001.BRANCH.BR_NAME EQ &BR_NAME.(, , , , , , , , , <'DOWNTOWN COEUR D''ALENE','DOWNTOWN COEUR D''ALENE'>, , , , , , , , , , , , , , , , , , , |FORMAT=A40V).Select a branch/department to run this report for:.QUOTEDSTRING;

By adding in single quotes around where the original ' should have been, it does bring back the correct name like seen above. I'm not sure if you could work a define to function the same way,
IF EMP.NAME EQ 'Thomas O'Toole' THEN 'Thomas O' 'Toole' ELSE EMP.NAME

Good luck!

Version: 8.2.03M, OS/Platform: Windows 7 & 10, Output: Excel, pdf, html

Dbeagan, Thanks for the idea but it gives me a lot more apostrophies but they aren’t in a consistent manner where I could manipulate them. For example, with Thomas O’Toole, I get this end result:
AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('''O''''Toole, Thomas''''')

And when I add a second name, I get this end result:
AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('''Osby, John'''' , ''''O''''Toole, Thomas''''')


LarissaB, Thanks for the idea but I don’t have just this one person with an apostrophe. I do have others. And we are really trying to not hardcode any names. But it’s definitely something that I will keep in the back of my mind. If no one on here has any ideas that end up working for me, I will have to explore this idea.

I believe Tony's suggestion was to add QUOTEDSTRING to the second mention of &FULL_LEADER_NAME:

-SET &FULL_LEADER_NAME = STRREP(2000,&LEADER_NAME,4,' OR ',3,' , ',990,A2000);
-SET &LEADER_LINE = 'AND ' | 'rtrim(D3.CRNT_LST_NM)' | ' || ' | ''', '' ' | '|| ' | 'rtrim(D3.CRNT_FRST_NM)' || ' IN( ' | '' || &FULL_LEADER_NAME.QUOTEDSTRING || ')';

The quotes would might then be passed properly to the SQL passthru query.

This message has been edited. Last edited by: Francis Mariani,

You may simplify &LEADER_LINE:

-SET &LEADER_LINE = 'AND ' | 'rtrim(D3.CRNT_LST_NM) || '', '' || rtrim(D3.CRNT_FRST_NM) IN( ' | '' || &FULL_LEADER_NAME.QUOTEDSTRING || ')';

I've added .QUOTEDLINE and it does not seem to make a difference. Once I did it I was getting the same results so I added a TYPE statement to see what it looks like. And whether I use the .QUOTEDSTRING or not, this is what my &LEADER_LINE looks like:
leader_line after set = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O'Toole, Thomas')

If you run exactly the code I provided as a standalone procedure, does it produce the kind of result you are looking for?

dbeagan, I worked more on your idea and it's closer than I had originally thought. It seems to be good if we were to select one person at a time. So just Thomas O'Toole works but when I add in another person, it doesn't. I tried to run this in a standalone but when I do that, when I try to hardcode in the values coming in like they do from the html page, they just don't always work the same. So I had to just build this idea out in my real fex. When I run just Thomas O'Toole with your code, it all looks like this:
);
-TYPE Start LEADER_LINE_INIT = 'O'Toole, Thomas'
Start LEADER_LINE_INIT = 'O'Toole, Thomas'
-SET &LEADER_LINE_INIT = SUBSTRING('O'Toole, Thomas', 2, 17-2);
-SET &LEADER_LINE_INIT = STRREP(2000, O'Toole, Thomas, 6, ''' OR ''', 1, '|', 2000, 'A2000');
, 1, '''', 2, '''''', 2000, 'A2000');
, 1, '|', 3, ''',''', 2000, 'A2000') || '''';
);
-TYPE Ending LEADER_LINE_INIT = 'O''Toole, Thomas'
Ending LEADER_LINE_INIT = 'O''Toole, Thomas'
-SET &LEADER_LINE = 'AND ' | 'rtrim(D3.CRNT_LST_NM)' | ' || ' | ''', '' ' | '|| ' | 'rtrim(D3.CRNT_FRST_NM)' || ' IN( ' | '' || 'O''Toole, Thomas' || ')';
-TYPE leader_line after set = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas')
leader_line after set = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas')
-SET &WHERECLAUSE1 = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas') ;

Once it’s put into the query, looks like:
AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas')

But once I add in another leader, that's when the apostrophes aren't working like I want. For example, here's Thomas plus John Osby:
);
-TYPE Start LEADER_LINE_INIT = 'O'Toole, Thomas' , 'Osby, John '
Start LEADER_LINE_INIT = 'O'Toole, Thomas' , 'Osby, John '
-SET &LEADER_LINE_INIT = SUBSTRING('O'Toole, Thomas' , 'Osby, John ', 2, 34-2);
-SET &LEADER_LINE_INIT = STRREP(2000, O'Toole, Thomas' , 'Osby, John, 6, ''' OR ''', 1, '|', 2000, 'A2000');
, 1, '''', 2, '''''', 2000, 'A2000');
, 1, '|', 3, ''',''', 2000, 'A2000') || '''';
);
-TYPE Ending LEADER_LINE_INIT = 'O''Toole, Thomas'' , ''Osby, John '
Ending LEADER_LINE_INIT = 'O''Toole, Thomas'' , ''Osby, John '
-SET &LEADER_LINE = 'AND ' | 'rtrim(D3.CRNT_LST_NM)' | ' || ' | ''', '' ' | '|| ' | 'rtrim(D3.CRNT_FRST_NM)' || ' IN( ' | '' || 'O''Toole, Thomas'' , ''Osby, John ' || ')';
-TYPE leader_line after set = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas'' , ''Osby, John ')
leader_line after set = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas'' , ''Osby, John ')
-SET &WHERECLAUSE1 = AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas'' , ''Osby, John ') ;

Once it’s in the query, it looks like:
AND rtrim(D3.CRNT_LST_NM) || ', ' || rtrim(D3.CRNT_FRST_NM) IN('O''Toole, Thomas'' , ''Osby, John ')

To come at this from a different angle could you replace all the apostrophes in you employee or input file with Acute Accents ( [Alt]180 )? They are not the same character but will your users notice? You also could do the reverse replace on your output file. You may have to check the high order characters in your output font to find the right character to use.