RE: preg_replace in update statement

[David Lerer]

 
Try with a combination of functions LOCATE and SUBSTR.
Locate will return the positions for WordA and WordB within the original text, 
and, SUBSTR will allow you to string what you you need all together.
David.

On Thu, Mar 8, 2012 at 4:11 PM, Hank  wrote:
> I have a simple problem:
>
> I have a varchar field in the database, and I want to remove all text
> between WordA and WordB, including WordA and WordB, leaving all text
> before WordA and after WordB intact.
>
> Possible with just SQL?  I know I can write a PHP program to do it,
> but it's not that important to spend that much time on.  I'd like one
> SQL statement to do it.
>
> Thanks!
>
> -Hank
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>



-- 
Baron Schwartz
Percona Inc 
Consulting, Training, Support & Services for MySQL

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql


The information contained in this e-mail and any attached
documents may be privileged, confidential and protected from
disclosure.  If you are not the intended recipient you may not
read, copy, distribute or use this information.  If you have
received this communication in error, please notify the sender
immediately by replying to this message and then delete it
from your system.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: query problem with null

[Johan De Meersman]

- Original Message -
> From: "David Lerer" 
> 
> Have you tried to set city = null   (i.e. without the quotes)?

Spot on, I'd think.

NULL values are not a string with "NULL" in it - that's only what it looks like 
in query results :-) An empty string ('') is to strings what 0 (zero) is for 
integers: it says "the value of this field is nothing". NULL, on the other 
hand, means "the value of this field is a total unknown", which is useful, for 
example, in a field 'quantity': zero is still a valid, meaningful quantity; 
whereas you would use NULL to indicate that you simply do not know the quantity.

It's a bit of a peculiar concept, but as David indicated, IS NULL will not 
match fields set to the string "NULL" - as that is a string, not an unknown.

Another funny attribute of NULL is that NULL != NULL. There simply *is* nothing 
to compare, so you cannot ever say it's equal.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: query problem with null

[Richard Reina]

Ahhh... Thank you, that was exactly what the problem was. I will fix the
code that is setting the value of these new records to 'NULL'.

Thank you.

2012/3/9 David Lerer 

> Have you tried to set city = null   (i.e. without the quotes)? David.
>
>
>
> -Original Message-
> From: Richard Reina [mailto:gatorre...@gmail.com]
> Sent: Friday, March 09, 2012 4:24 PM
> To: mysql@lists.mysql.com
> Subject: query problem with null
>
>  When I do the following query:
>
> SELECT * FROM geo_trivia WHERE city IS NULL;
>
> certain columns that DO have 'NULL' value for city and not a '' (blank)
> value do not show up.
> I have even gone to the extent of reseting these records value as ='NULL'
> with UPDATE and they are still are not selected when I run the above
> query.  Can anyone help?
>
> The information contained in this e-mail and any attached
> documents may be privileged, confidential and protected from
> disclosure.  If you are not the intended recipient you may not
> read, copy, distribute or use this information.  If you have
> received this communication in error, please notify the sender
> immediately by replying to this message and then delete it
> from your system.
>

RE: query problem with null

[David Lerer]

Have you tried to set city = null   (i.e. without the quotes)? David.



-Original Message-
From: Richard Reina [mailto:gatorre...@gmail.com] 
Sent: Friday, March 09, 2012 4:24 PM
To: mysql@lists.mysql.com
Subject: query problem with null

 When I do the following query:

SELECT * FROM geo_trivia WHERE city IS NULL;

certain columns that DO have 'NULL' value for city and not a '' (blank)
value do not show up.
I have even gone to the extent of reseting these records value as ='NULL'
with UPDATE and they are still are not selected when I run the above
query.  Can anyone help?

The information contained in this e-mail and any attached
documents may be privileged, confidential and protected from
disclosure.  If you are not the intended recipient you may not
read, copy, distribute or use this information.  If you have
received this communication in error, please notify the sender
immediately by replying to this message and then delete it
from your system.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: preg_replace in update statement

[Baron Schwartz]

You may find this helpful: http://www.mysqludf.org/lib_mysqludf_preg/

On Thu, Mar 8, 2012 at 4:11 PM, Hank  wrote:
> I have a simple problem:
>
> I have a varchar field in the database, and I want to remove all text
> between WordA and WordB, including WordA and WordB, leaving all text
> before WordA and after WordB intact.
>
> Possible with just SQL?  I know I can write a PHP program to do it,
> but it's not that important to spend that much time on.  I'd like one
> SQL statement to do it.
>
> Thanks!
>
> -Hank
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>



-- 
Baron Schwartz
Percona Inc 
Consulting, Training, Support & Services for MySQL

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

query problem with null

[Richard Reina]

 When I do the following query:

SELECT * FROM geo_trivia WHERE city IS NULL;

certain columns that DO have 'NULL' value for city and not a '' (blank)
value do not show up.
I have even gone to the extent of reseting these records value as ='NULL'
with UPDATE and they are still are not selected when I run the above
query.  Can anyone help?

RE: A Blob That's a String??

[Jack Hatterly]

> Date: Fri, 9 Mar 2012 13:43:11 +0100
> From: h.rei...@thelounge.net
> To: mysql@lists.mysql.com
> Subject: Re: A Blob That's a String??
> 
> where is the difference of "binary" and "string" after fetch it from DB?
> the result IS a binary safe string!

However, when I change this line in my code:

content = cursor.fetchall()[0][0].tostring()

to this:

content = cursor.fetchall()[0][0]

I get this error:



What's frustrating is that this code had worked before. That is why I ask, how 
do I test that MySQL has uploaded a binary file?
TIA,
Jack
  

Re: A Blob That's a String??

[Reindl Harald]

Am 09.03.2012 13:28, schrieb Jack Hatterly:

> Even when I hard code in the correct values I get this error. However, when I 
> select * from pics, where there is only one longblob in the table, it prints 
> out a tremendous load of data which indicates to me that a binary file has 
> indeed been loaded. What I am trying to ascertain is why is this longblob 
> considered a string? How can I more properly test if a binary file has been 
> loaded?

where is the difference of "binary" and "string" after fetch it from DB?
the result IS a binary safe string!





signature.asc
Description: OpenPGP digital signature

A Blob That's a String??

[Jack Hatterly]

Hi;
I have loaded an image from a form into MySQL using Python as a longblob. Here 
is my table:

mysql> describe pics;
+-++--+-+-++
| Field   | Type   | Null | Key | Default | Extra  |
+-++--+-+-++
| ID  | int(11)| NO   | PRI | NULL| auto_increment | 
| Specials_ID | int(11)| NO   | MUL | NULL|| 
| Number  | tinyint(1) | YES  | | NULL|| 
| Pic | longblob   | YES  | | NULL|| 
+-++--+-+-++

Here is my Python code that loads the data:

pic_zero = form.getfirst("pic_zero") # where pic_zero is an uploaded file 
(i.e., image) from a form calling this script
...
pics = [pic_zero, pic_one, pic_two, pic_three]
i = 0
for pic in pics:
  if pic:
sql = 'insert into pics values (Null, "%s", "%s", "%s")' % (max_id,  i, 
"%s")
cursor.execute(sql, (MySQLdb.Binary(pic),), )
db.commit()
  i += 1

Here is the Python code that calls the image:

#!/usr/bin/python
import cgitb; cgitb.enable()
import MySQLdb
import cgi
import sys,os
sys.path.append(os.getcwd())
from login import login
user, passwd, db, host = login()
form = cgi.FieldStorage()
id = form['id'].value
number = form['number'].value
db = MySQLdb.connect(host, user, passwd, db)
cursor= db.cursor()
cursor.execute("select Pic from pics where ID=%s and Number=%s", (id, number))
content = cursor.fetchall()[0][0].tostring()
cursor.close()
print 'Content-Type: image/jpeg'
print
print content

Here is the Python error:

   14 cursor= db.cursor()


   15 cursor.execute("select Pic from pics where ID=%s and Number=%s", (id, 
number))


   16 content = cursor.fetchall()[0][0].tostring()


   17 cursor.close()


   18 print 'Content-Type: image/jpeg'


content undefined, cursor = , cursor.fetchall = 
>, ].tostring 
undefinedAttributeError: 'str' object has no attribute 'tostring'

  args =
("'str' object has no attribute 'tostring'",)




Even when I hard code in the correct values I get this error. However, when I 
select * from pics, where there is only one longblob in the table, it prints 
out a tremendous load of data which indicates to me that a binary file has 
indeed been loaded. What I am trying to ascertain is why is this longblob 
considered a string? How can I more properly test if a binary file has been 
loaded?
TIA,
Jack
  

ANN: Code Factory for MySQL 12.3 released

[SQL Maestro Team]

Hi!

SQL Maestro Group announces the release of Code Factory for MySQL
12.3, a powerful Windows GUI solution aimed at the MySQL queries and
scripts development.

The new version is immediately available for download at
http://www.sqlmaestro.com/products/mysql/codefactory/

Please also note that before March 18 you can get an additional 20%
discount on purchasing any our product suite including recently
launched Web Developer bundles.
http://www.sqlmaestro.com/purchase/

Top 5 New features
=

1. SQL Editor: Unicode support, text change markers, code snippets,
and improved code folding appearance.
2. Creation of views from Visual Query Builder.
3. Data import from any database accessible via ODBC driver / OLE DB provider.
4. Managing BLOB data as PDF.
5. Spanish translation.

In addition to this, several bugs have been fixed and some other minor
improvements and corrections have been made. Full press-release is
available at:
http://www.sqlmaestro.com/news/company/code_factory_advanced_to_12_3/

Background information:
---
SQL Maestro Group offers complete database admin, development and
management tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2,
SQLite, SQL Anywhere, Firebird and MaxDB providing the highest
performance, scalability and reliability to meet the requirements of
today's database applications.

Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql