Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 2005 > C# 2005
|
C# 2005 For discussion of Visual C# 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 9th, 2007, 08:14 AM
GS GS is offline
Authorized User
 
Join Date: Sep 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default StringBuilder select statement

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.
 
Old August 9th, 2007, 08:40 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
 
Old August 9th, 2007, 09:44 AM
GS GS is offline
Authorized User
 
Join Date: Sep 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Brilliant. thank you very much

 
Old August 25th, 2007, 02:01 PM
Registered User
 
Join Date: Aug 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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".


 
Old August 26th, 2007, 01:42 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Select In Statement mr_pc1963 SQL Server 2005 2 April 19th, 2008 09:59 AM
select Statement gregalb SQL Server 2000 3 January 15th, 2008 12:00 AM
select statement help... RinoDM SQL Server 2000 13 January 10th, 2008 08:34 PM
Select statement.. help please sarah lee SQL Server 2000 4 May 16th, 2007 09:13 AM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.