StringBuilder select statement

GS · August 9th, 2007, 08:14 AM

All I have a problem with creating a select statement.

The statement is basically looking through a field and stripping out special characters and putting in ' '.The statement used on the database is (and works fine):

SELECT OPPORTUNITY_ID, ACCOUNT_NAME, translate(OPPORTUNITY_NAME, '/";:<>|[],.''?~`!$%^&*()-+={}\,Â£',' ')
OPPORTUNITY_MANAGER FROM EPM_OPPORTUNITY_VW

I tried:
StringBuilder sb = new StringBuilder("SELECT OPPORTUNITY_ID, ACCOUNT_NAME, translate(OPPORTUNITY_NAME, '/";:<>|[],.''?~`!$%^&*()-+={}\,Â£',' '), OPPORTUNITY_MANAGER FROM EPM_OPPORTUNITY_VW ");

But the quotes and are providing a problem. Any ideas?

Thanks in advance.

joefawcett · August 9th, 2007, 08:40 AM

You need to escape the inner quotes (") AND THE BACKSLASH (\): "SELECT OPPORTUNITY_ID, ACCOUNT_NAME, translate(OPPORTUNITY_NAME, '/\";:<>|[],.''?~`!$%^&*()-+={}\\,Â£',' '), OPPORTUNITY_MANAGER FROM EPM_OPPORTUNITY_VW "

--

Joe (Microsoft MVP - XML)

GS · August 9th, 2007, 09:44 AM

Brilliant. thank you very much

NicoDeBruin · August 25th, 2007, 02:01 PM

Quote:

quote:Originally posted by GS
I tried:
StringBuilder sb = new StringBuilder("SELECT OPPORTUNITY_ID, ACCOUNT_NAME, translate(OPPORTUNITY_NAME, '/";:<>|[],.''?~`!$%^&*()-+={}\,Â£',' '), OPPORTUNITY_MANAGER FROM EPM_OPPORTUNITY_VW ");

Take care that no character has a special meaning. If you want to be sure that every character in the string is seen as a "character" start the string with an "@", for instance

StringBuilder sb = new StringBuilder(@"SELECT OPPORTUNITY_ID, ACCOUNT_NAME, translate(OPPORTUNITY_NAME, '/";:<>|[],.''?~`!$%^&*()-+={}\,Â£',' '), OPPORTUNITY_MANAGER FROM EPM_OPPORTUNITY_VW ");

In this case none of the characters is interpreted as a "special character" or "escape sequence".

planoie · August 26th, 2007, 01:42 PM

I see you have a back tick (`) in your list of characters to replace. If you also want to support replacement of the forward tick/single quote (') you *will* need to escape it for the SQL command itself. Use 2 single quotes in your command string to escape the character in the database engine. Thus, '' becomes ' in your list of translated characters.

-Peter