Solved

Trim MySQL string problem

Posted on 2006-10-23
8
513 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

803 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