Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Trim MySQL string problem

Posted on 2006-10-23
8
514 Views
Last Modified: 2007-12-19
Experts,

I am trying to trim an SQL query string.  I have done the following which works good;

SQL = SQL.Replace("  ", "")

I also need to replace the character '     however My SQL statements require this, but any of my variables which pull info into the SQL string could possibly contain this character and therefore the following would mess up the query

 'SupportPC', 'intel's pentium'

Intel's contains this character.

Is there a way around this?
0
Comment
Question by:nickmarshall
  • 4
  • 3
8 Comments
 
LVL 10

Expert Comment

by:gangwisch
ID: 17789394
yes in MSSQL you do
SQL = SQL.Replace("'", "''")
in MySQL
i think you do SQL = SQL.Replace("'", "'\")
please try both and see if any progress is made
0
 
LVL 5

Expert Comment

by:proten
ID: 17789403
I would say your best bet would be to escape the ' char before you add it to the sql string.  You can replace the ' in a string to a double '' (not quote, 2 tics) to allow sql to read it, but there is no good way to be able to pick out the quote tics from the string tics.
0
 
LVL 1

Author Comment

by:nickmarshall
ID: 17789511
Hi,

I am trying to convert XML nodes to string so that I can check for bad characters;

        Dim SoundManufacturer As XmlNode = XmlDoc.DocumentElement.SelectSingleNode("//Sound/Manufacturer")
        SoundManufacturer.InnerText.ToString()

How do I set the above to a string, I though ToString would already make it a string.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 5

Expert Comment

by:proten
ID: 17789601
You wouldn't even need the tostring as the InnerText property is defined as a string.

so:
s = SoundManufacturer.InnerText.Replace("'", "''")
0
 
LVL 1

Author Comment

by:nickmarshall
ID: 17789610
Just tried the above with the second line and it still works, so this is not doing anything?

How would I trim the first line here?
0
 
LVL 5

Expert Comment

by:proten
ID: 17789639
To trim you would use the following

Trim(SoundManufacturer.InnerText)
0
 
LVL 1

Author Comment

by:nickmarshall
ID: 17789657
I get "S" not declared when using;

s = SoundManufacturer.InnerText.Replace("'", "''")
0
 
LVL 5

Accepted Solution

by:
proten earned 250 total points
ID: 17789676
s was just an example of the variable to place it into

i.e
Dim s As String
s = SoundManufacturer.InnerText.Replace("'", "''")
s will contain the text of SoundManufacturer.InnerText with a single ' replaced by double.

You can use:
SoundManufacturer.InnerText = SoundManufacturer.InnerText.Replace("'", "''")
to replace the text in the actual node.

If you post your code and what you want to do I'll post the code you should use.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question