Trim MySQL string problem

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?
LVL 1
nickmarshallAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gangwischCommented:
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
protenCommented:
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
nickmarshallAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

protenCommented:
You wouldn't even need the tostring as the InnerText property is defined as a string.

so:
s = SoundManufacturer.InnerText.Replace("'", "''")
0
nickmarshallAuthor Commented:
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
protenCommented:
To trim you would use the following

Trim(SoundManufacturer.InnerText)
0
nickmarshallAuthor Commented:
I get "S" not declared when using;

s = SoundManufacturer.InnerText.Replace("'", "''")
0
protenCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.