Solved

Trim MySQL string problem

Posted on 2006-10-23
8
517 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
[X]
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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

617 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