Solved

Trim MySQL string problem

Posted on 2006-10-23
8
515 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
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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

680 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