Re: Converting string hex column to integer
On 28.06.2006 13:54, DuĊĦan Pavlica wrote:
Hello,
I have column of type char(2) containing hex numbers (e.g. 0A, FF,
...) and I cannot find correct function which could convert those hex
numbers to integers so I can perform futher calculations. I experimented
with HEX(), CAST(), CONVERT() but I wasn't succesfull.
Thanks in advance,
Dusan
Use conv:
mysql select conv('11', 16, 10);
++
| conv('11', 16, 10) |
++
| 17 |
++
1 row in set (0.01 sec)
mysql select conv('0a', 16, 10) + conv('a0', 16, 10);
+-+
| conv('0a', 16, 10) + conv('a0', 16, 10) |
+-+
| 170 |
+-+
See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
HTH,
Wolfram
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
Barry wrote: Hello everyone! I have a slight problem matching rows. My problem is the Value in a textfield is: 87682next39857 I created that with concat. Is there a way to match one specific number out of that field? like WHERE SUPERFUNCTION(concated_field) = 87682 WHERE concated_field LIKE '%87682%' See: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html BTW: 1. The performance of LIKE is not that good :-S 2. This doesn't sound like a good DB-Design, why don't you use two seperated fields for both numbers, or a m:n table if there are more possible entries? Is something like that possible in any way? Or does something like that function exists? Well in PHP you a function called in_array() which would work kind of similiar what i want to do. Any help is very appriciated ^_^ Many thanks for any replies Barry HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching problem
Marcus Bointon wrote: On 9 May 2006, at 14:27, Wolfram Kraus wrote: WHERE concated_field LIKE '%87682%' No, because that would also match numbers that contain that sequence like '187682next32876825'. WHERE concated_field LIKE '87682%' OR concated_field LIKE '%87682' Still poor performance ;-) [...] Marcus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Force a COMMIT on InnoDB tables?
patrick wrote: I'm wondering if there's any way to force updates on InnoDB tables to require an explicit COMMIT when running queries from the mysql command-line client (similar to Oracle's command line client)? set autocommit = 0 See http://dev.mysql.com/doc/refman/5.0/en/innodb-and-autocommit.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Histogram from tables.
Mike Martin wrote: I have a large table of filenames and creation dates from which I want to produce a histogram. SELECT year(date), quarter(date), count(0) FROM pics WHERE date(date) '2000' AND date(date) ' 2005' GROUP BY year(date), quarter(date) Gets me close, but to simplify plotting, I want to include rows for the dates where no files were created. I get this: ++---+--+ | year(date) | quarter(date) | count(0) | ++---+--+ | 2001 | 3 | 34 | | 2002 | 1 |2 | | 2002 | 4 |1 | | 2003 | 2 |1 | | 2003 | 3 |1 | | 2003 | 4 |3 | | 2004 | 1 |1 | | 2004 | 2 |1 | | 2004 | 3 |5 | | 2004 | 4 |1 | ++---+--+ I want this: ++---+--+ | year(date) | quarter(date) | count(0) | ++---+--+ | 2001 | 1 |0 | | 2001 | 2 |0 | | 2001 | 3 | 34 | | 2001 | 4 |0 | | 2002 | 1 |2 | | 2002 | 2 |0 | | 2002 | 3 |0 | | 2002 | 4 |1 | | 2003 | 1 |0 | | 2003 | 2 |1 | | 2003 | 3 |1 | | 2003 | 4 |3 | | 2004 | 1 |1 | | 2004 | 2 |1 | | 2004 | 3 |5 | | 2004 | 4 |1 | ++---+--+ Thanks in advance for your help! MikeMartin Take a look at WITH ROLLUP: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.0 always executes case insensitive queries
Nico Sabbi wrote: Hi, my mysql always executes case insensitive queries: SELECT username FROM workflow.user WHERE username = 'NicO' LIMIT 1; +--+ | username | +--+ | nico | +--+ 1 row in set (0.01 sec) that field is of varchar(255) type. I don't understand the reason for this behavior. What should I check? Thanks, Nico You need the binary function: http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can we run linux commands from inside the msql client
Sujay Koduri wrote: hi.. I searched the documentation and googled for sometime, but didnt find anything related to this can we execute shell commands inside mysql client (like using ! in oracle), and if possible please tell me how. sujay Use \! E.g. \! ls More information :\? HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: port option ignored by commands
[EMAIL PROTECTED] wrote: Hi, using mysql 4.0.22-standard-log. one instance on port 3307 one instance on port 3306 (each binary in its own independent directory) unix 'root' account submits following commands and connects to mysql on default 3306 port instead of awaited 3307 port : ~mysqld/bin/mysql --port=3307 -p ~mysqld/bin/mysqldump -u root -p demo -a --add-drop-table --add-locks --opt -P3307 /tmp/demotest.sql using '--port' or '-P' gives the same result. This was tested without any .my.cnf file. Then I made a test with a config file : ~mysqld/bin/mysql --defaults-extra-file=my3307.cnf [client] password = mypassword port= 3307 This file was read : no password prompt. But I accessed the instance on port 3306. ( unix 'mysqld' account accesses properly the 3307 instance with option specified in .my.cnf file) What am I missing ? Is that a known bug ? similar to bug#5792 ? Thanks for your help, Andrew Use the -h option for the host, otherwise mysql tries to connect via socket: ~mysqld/bin/mysql --port=3307 -p -h 127.0.0.1 HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto SQL
Winanjaya wrote: Dear MySQL Experts, I am using MySQL 4.1, I have a database with more than 100 tables inside, is there any 3rd party tools that can help me to create a SQL statement of CREATE TABLE blah blah blah for each table in my DB . please advise Regards Winanjaya mysqldump -d --add-drop-table=0 -n -u USERNAME -p DATABASE Replace USERNAME DATABASE with your actual values. For more options see: mysqldump --help HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left join results - can we make it produce at most 1 row?
Joshua Beall wrote: Hi All, I have two tables in a children's program registration system, parent and child. 1 row in the parent table corresponds to 1 or more rows in the child table. Right now, when I do SELECT * FROM parent LEFT JOIN child ON parent.ID = child.parentID I get multiple rows for each parent if they registered more than one child. This is normal and expected I know, but I am wondering is there a way to return the children on the *same* row, so that the first bit of the row is the parent's information, and the second part of the row is something like child1.fname, child1.lname, child2.fname, child2.lname, etc. I'm not really tied to LEFT JOIN if I have to use another syntax. I am also not concerned about speed, because this query would be run at most a few times per week by an admin. It can be inefficient - as long as it won't crash the server! :-) Any help? Sincerely, -Josh Use GROUP BY with GROUP_CONCAT: http://dev.mysql.com/doc/mysql/en/group-by-functions.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left join results - can we make it produce at most 1 row?
Joshua Beall wrote: Wolfram Kraus [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Use GROUP BY with GROUP_CONCAT: http://dev.mysql.com/doc/mysql/en/group-by-functions.html Thanks, I'll take a look and see if I can figure that out. I wasn't aware of the GROUP_CONCAT function; it looks like that might be just what I need. Any examples that apply to my situation would be greatly appreciate! :-) Sincerely, -Josh Untested!!! (No 4.1 available here) SELECT GROUP_CONCAT(child1.fname) FROM parent LEFT JOIN child ON parent.ID = child.parentID GROUP BY(parent.ID) You may also need some string functions: http://dev.mysql.com/doc/mysql/en/string-functions.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RETURNING Keyword?
Justin Burger wrote: Good Afternoon, In Oracle there is a keyword called RETURNING, where I can do an insert and specify what row to return, this helps with autoincrement fields; How can I achieve this in MySQL? If you only want to know the value of the last inserted autoinc field, use LAST_INSERT_ID: http://dev.mysql.com/doc/mysql/en/information-functions.html Thanks a bunch!! -- Justin W. Burger HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple concurrency problem - any advice ??
Heyho! Tim Wood wrote: Hi I have a number of clients connecting to a DB in order to take jobs off a queue, mark them active, then run them. In pseudo code, each client executes the following sequence of queries: a-- select test_id from tests where status=1 and priority 11 order by priority b-- update tests set status=2 where test_id = result of query above load,run test etc [ eg status==1 - queued, status==2 - active] What happens in some cases is that tests can be marked active by more than one client. I'm guessing what is happening (if we have two clients C1, C2 ) is the following sequence of events in the DBMS: C1a C2a C1b C2b What's the best way to ensure that a single client executes both a and b atomically so that no other client can show up and execute an 'a' between another client's a and b ? That is what transactions are for, you need INNODB-Tables and BEGIN/START TRANSACTION and COMMIT (in combination with SET AUTOCOMMIT=0): http://dev.mysql.com/doc/mysql/en/COMMIT.html http://dev.mysql.com/doc/mysql/en/Transactional_Commands.html http://dev.mysql.com/doc/mysql/en/InnoDB_transaction_model.html This is one part of the ACID-Priniple: http://en.wikipedia.org/wiki/ACID If your application does this in a multithreaded way beware of deadlocks (see the INNODB chapter above)! Cheers Tim HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replacing field contents?
Heyho! Robin Lynn Frank wrote: I hope this question doesn't sound too dumb, but since I can't afford to screw it up... I have a table with 98,000 records. I want to globally replace the content of one field where the content is XXX diverse_text with XXX non-diverse_new_text In other words, if the content of the field starts XXX, I want it replaced with XXX with only the new text following. Use update: UPDATE your_tbl SET the_field = 'XXX non-diverse_new_text' WHERE the_field = 'XXX%' http://dev.mysql.com/doc/mysql/en/String_functions.html Beware! Make a backup of your database before you try this! (NB: Don't believe everything you read untested) TIA -- Robin Lynn Frank - Director of Operations - Paradigm-Omega, LLC HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sorting doesn't work correctly with UTF-8 data
Erol YILDIZ wrote: Hi, I have mysql-4.0.18 installed and entered data with UTF-8 characters. When I use a Select command, mysql doesn't sort the data correctly which starts with native Turkish letters. Is there a way to fix it? MySQL 4.0.x doesn't support UTF-8, you need MySQL 4.1.x (4.1.8 is the altest version) to handle UTF-8. Thanks.. -- Erol YILDIZ HEV Schools Kemerkoy http://www.kemerkoy.k12.tr HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dates range query
Brian Menke wrote: Hi everyone, I've always had a challenge working with dates. I'm building an app that needs to query a range of dates. I'm using ASP (for the first time, I usually write in Java). Anyway my date column uses the -00-00 format, which I think is the default format? Is it? Thats the ISO date format, an unambiguous format for date. I've read different approaches doing a google search, but am more confused than ever. I have used several date functions in java, but want to know if I can somehow use date functions in a regular sql query to somehow reformat the dates so I can query a range of dates. Does that make sense? Can someone give me an example of how to do this please? I'm sure people do this all the time, but it confuses the heck outta me :-) If you want to query a range of dates you can do something like this: select * from tbl where datfield 2005-01-06 and datfield 2004-12-01 For more information about date-/time-functions see the excellent manual: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Thanks! -Brian Menke HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how escape special in a field - fixed ( null plus something = ? )
YW CHAN (Cai Lun e-Business) wrote:
Gleb,
Thanks, eventually I find that the problem is not the special.
But the statement concat(field1,',',field2) as something will
generate NULL if the field1 is NULL.
Not sure if it's documented anywhere, or just a simple programming
concept that null plus anything equal null..
Yes, that is documented. You can use concat_ws(',', field1, field2)
See: http://dev.mysql.com/doc/mysql/en/String_functions.html
HTH,
Wolfram
-- Original Message --- From: Gleb Paharenko
[EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Mon, 13
Dec 2004 13:44:42 +0200 Subject: Re: how escape special in a field
Hello.
See:
http://dev.mysql.com/doc/mysql/en/mysql_real_escape_string.html
Can you send complete test for your problem?
YW CHAN (Cai Lun e-Business) [EMAIL PROTECTED] wrote:
Hi,
I find there seems problem with this select statement when there
is a special character inside the table. i.e.
select concat(field_1, ',', field_2) as name where ...
field_2 actually is something like 'George, Banson' ( with a
comma in
between )
i guess this , destroy the SQL syntax.
Is there any function for protecting this situation?
Thanks for your help.
Regards, CHAN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Results question
Stuart Felenstein wrote: I have a select, from, where, query set up. There are a number of inner joins in it as well. Now what I noticed is if there are some null fields in the records, nothing will get returned. If I remove those particular joins (where the NULLS are), the record is returned. Does this sounds like a join issue ? The tables that are joined are interpretive tables. Meaning I store a 1 for Alabama in the main table. The States table I join has the 1 and then the associate label. Stuart You need LEFT JOIN to get results for the lines that contain NULL values in the joined table. http://dev.mysql.com/doc/mysql/en/JOIN.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
[...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. I don't say that the example is bad, I only said that in MySQL you can do this without a SP. Mike Wolfram Wolfram Kraus wrote: [...] Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. [...] Pardon my ignorance, but why can't you do this (in MySQL) with a select count ... and afterwards a select ... order by... LIMIT? All the work is done on the server, too. No need for a SP here. Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure?
Heyho! [EMAIL PROTECTED] wrote: news [EMAIL PROTECTED] wrote on 11/30/2004 07:58:18 AM: Michael J. Pawlowsky wrote: Because you will be downloading 500,000 rows... And I don't really think that was the point. Who cares what the example is. Personally I was quite impressed with great explanation. I second that it was a great explanation! Please don't get me wrong! And furthermore I will only download one row two times: select count - one row select ... limit count/2, 1 - one row Who's to say that his SP can't use your method and work just that much faster? That would be the optimal solution for MySQL 5.x ;-) The point was that stored procedures can automate complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other complex tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. My original posting was a little bit short, sorry for that! I know what SPs are, I only wanted to point out that you don't need SPs to get the median without heavy calculations on the client. The definition of user levels/roles is another story. Btw: Rhino was missing/hiding the part with hiding complexity from users in his excellent explanation. I would have simply said a chunk of code that runs on the server that the client can call. And this guy took the time to put together a really good reply with an example. I don't say that the example is bad, I only said that in MySQL you can do this without a SP. Yes, but as I mentioned above, that would require some modest SQL skills from the user writing the query. Not all users are as comfortable with SQL as we are as administrators. Even if you give them some cut-and-paste code that did this function, they would still need use it properly. This is especially difficult for those users who rely on visual query builders (GUI interfaces) to automate their SQL generation. But, If I give them the name of a stored procedure that reliably computes what they need then the time I spend helping those who don't want to learn SQL to write useful queries goes down considerably. Point taken, nice example ;-) I am not really an DBA, I am more like a db-user (not in your way of definition) ;-) As I said above: definition of user levels/roles are a complete different thing. Mike Wolfram Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of connections to a database
Philippe de Rochambeau wrote: Hello, is there any way to tell the number of simultaneous connections to a mysql database at a given time, using SQL or a scripting language such as php, perl, etc. ? Many thanks. Philippe show status is your friend: show status like Connections HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Redirect output to the file
Jerry Swanson wrote: I want to redirect output of the query to the file in tab delimited format. Can this be done? Thanks Select ... INTO OUTFILE: http://dev.mysql.com/doc/mysql/en/SELECT.html or mysql -e (documentation on the same page) HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub queries
Nathan Coast wrote: Hi apologies if this is a dumb question but can you do subqueries in mysql? select count(*) as RES from ACL_USER_GROUP_ROLE as UGR where UGR.USER_ID =2 and UGR.ROLE_ID = (select ROLE_ID from ACL_ROLE where ROLE_NAME = 'projectmanager' ) this query fails, but the individual queries work fine select count(*) as RES from ACL_USER_GROUP_ROLE as UGR where UGR.USER_ID =2 and UGR.ROLE_ID = 3 and select ROLE_ID from ACL_ROLE where ROLE_NAME = 'projectmanager' cheers Nathan You need MySQl 4.1.x to do subqueries. HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Preserving backslashes in DML
Tom Kirkman wrote: What are the options available for inserting\updating a MySQL table VARCHAR with a string containing backslash characters so that the backslash characters are preserved as is? For example, the UNC string '\\MyServer\MyDir file:///\\MyServer\MyDir ' would be changed on the way in to the VARCHAR to become '\MyServerMyDir'. What options are there for specifying that this changing on the way in should NOT be done? Just mask every backslash with another backslash: 'MyServer\\MyDir' http://dev.mysql.com/doc/mysql/en/String_syntax.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple return values from SP or workaround ?
Wim Verhaert wrote: Is there any way to return multiple result variables from a stored procedure? And I guess the answer is NO. Or does anyone know how I can Concatenate (using the CONCAT function) to glue together my multiple results into one string that then can be post processed in perl. EG: query on table names returns multiple names: +-+ | Name| +-+ | name1 | | name2 | | name3 | +-+ now the result returned should look something like name1::name2::name3 Thanks Wim If you can write your query with group by, GROUP_CONCAT may help you: http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html HTH Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
