Re: finding count of spaces in a string
[EMAIL PROTECTED] ha scritto: We have numerous identical tables with a varchar column that holds data like this: 0 0 0 1 0 1 0 25 7 0 139 0 9. Essentially there are a bunch of integers with a single space as a separator. There _should_ be no more than 30 entries ( and 29 spaces ), but sometimes the system misfires and there are more or less. Is there a MySQL solution to getting a count of the spaces present in the field, figuring that spaces + 1 will equal entries? It's fairly straight forward using a PHP application, but I'd like to get the DB server to accomplish this task. Not having much luck finding a solution in the manual. SELECT 1 + CHAR_LENGTH(0 0 0 1 0 1 0 25 7 0 139 0 9) - CHAR_LENGTH(REPLACE(0 0 0 1 0 1 0 25 7 0 139 0 9, , )) AS ret HopeItHelp, Francesco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not Sorting Correctly
It's possible to repeat with a varbinary field also in 5.1
CREATE TABLE t1 (
RoomNo varbinary(10) DEFAULT NULL,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL,
ChapterID int(11) NOT NULL DEFAULT '358'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t1` (`RoomNo`, `LastName`, `FirstName`, `ChapterID`) VALUES
('1', 'Anderson', 'Kayla', 358),
('2', 'Barton', 'Greg', 358),
('3', 'Beaty', 'Brooke', 358),
('1', 'Brown', 'Paige', 358),
('2', 'Bynum', 'Wesley', 358),
('8', 'Clark', 'Andrew', 358),
('8', 'Clark', 'Ramsey', 358);
The solution are I've found are 2, the first convert the field to utf8,
the second to use lpad function instead of concat, this inherently work
(but don't ask why).
-- explicitly convert it to utf8
SELECT CONVERT( (RIGHT(CONCAT('000',RoomNo),3)) USING utf8) AS
PaddedRoomNo, LastName, FirstName
FROM t1
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY PaddedRoomNo, LastName, FirstName
-- pad server side (implicitly convert to utf8 ?)
SELECT LPAD(RoomNo,3,'0')AS PaddedRoomNo, LastName, FirstName
FROM t1
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY PaddedRoomNo, LastName, FirstName
Jesse ha scritto:
Strange. I'm running the same exact version, and it's not the same.
What field types are you using? Mine are as follows:
RoomNo VarChar(10)
LastName VarChar(25)
FirstName VarChar(25)
the values that I put into Room No are 1,2,3, etc. I'm not
storing 001,002,003, etc in there.
Jesse
- Original Message - From: Zhaowei [EMAIL PROTECTED]
To: Jesse [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, March 23, 2007 5:03 AM
Subject: Re: Not Sorting Correctly
Hi, Jesse,
I did a small test and found it was in order. My version is
+-+
| version() |
+-+
| 5.0.22-community-nt-log |
+-+
select right(concat('000',text_id),3) AS text,name from
an order by right(concat('000',text_id),3),name;
+--+-+
| text | name|
+--+-+
| 001 | cat |
| 001 | dog |
| 001 | monkey |
| 001 | rat |
| 001 | wolf|
| 002 | cat |
| 002 | whale |
| 003 | lax |
| 003 | penguin |
| 006 | ostrich |
+--+-+
10 rows in set (0.00 sec)
On 3/23/07, Jesse [EMAIL PROTECTED] wrote:
When I run the following query:
SELECT RIGHT(CONCAT('000',RoomNo),3),LastName,FirstName
FROM ConfHotelDet
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY RIGHT(CONCAT('000',RoomNo),3), LastName, FirstName
I get the following result:
001AndersonKayla
002BartonGreg
003BeatyBrooke
001BrownPaige
002BynumWesley
008ClarkAndrew
008ClarkRamsey
Etc...
As you can see, it's out of order.
Jesse
- Original Message -
From: Ales Zoulek [EMAIL PROTECTED]
To: Jesse [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, March 19, 2007 9:06 PM
Subject: Re: Not Sorting Correctly
pls, post result of:
SELECT RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName FROM
Ales
On 3/19/07, Jesse [EMAIL PROTECTED] wrote:
I have an app that I've converted to MySQL from MS SQL. I used
to use
the
following to force a Alpha field to sort as if it were numeric (I
know,
perhaps it is better if I made the field numeric to begin with,
but it's
not, and I don't remember why, but that's not the question here):
ORDER BY RIGHT('000' + RoomNo,3),LastName,FirstName
I converted this to the following in MySQL:
ORDER BY RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName
In MS SQL, it would sort correctly:
1 Kayla Andre
1 Paige Brackon
1 Kasie Guesswho
1 Katelyn Hurst
2 Craig Bartson
2 Wesley Bytell
2 Kevin Peterson
2 Bryan Wilton
etc...
Now, the Above (RIGHT(CONCAT...)-MySQL Version), seems to ignore the
first
sort field, and simply sorts alphabatically:
1 Kayla Andre
2 Craig Bartson
1 Paige Brackon
2 Wesley Bytell
1 Kasie Guesswho
1 Katelyn Hurst
2 Kevin Peterson
2 Bryan Wilton
I finally ended up with:
ORDER BY CAST(RoomNo AS UNSIGNED), LastName, FirstName
Which works perfectly, but I'm just wondering why the first attempt
(right(concat...)) didn't work?? Any ideas?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
--
Ales Zoulek
NetCentrum s.r.o.
+420 739 542 789
+420 604 332 515
ICQ: 82647256
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Best Regards,
Yours Zhaowei
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
Maybe this one could do (untested) ? SELECT COUNT(*), ( SELECT count(*) FROM employees AS t2 WHERE t2.name = 'Joe' AND t2.state = 'ME' AND t2.hiredate = datevalue) AS joesexist FROM employees WHERE name = 'Joe' AND state = 'ME' AND hiredate = datevalue; Jerry Schwartz ha scritto: I don't think that will work. If there are 1,000 records that qualify but none for Joe, then it will return 1,001. If Joe is in record 1 of the retrieved record set, and there are 999 other people who match the WHERE clause, then it will retrieve 1,000. Am I missing something? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, March 22, 2007 12:33 PM To: James Tu Cc: MySQL List Subject: Re: Finding a record in a result set I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) If 'Joe' is a unique name LOL... SELECT 1 + COUNT(*) FROM employees WHERE name 'Joe' AND state = 'MA' AND hiredate datevalue; PB James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: 3/22/2007 7:44 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Functions and NULL with standard install
select rpad(|IFNULL(|null, ''),5,'1');
|/||/|
[EMAIL PROTECTED] ha scritto:
I am running a basic install of MySQL 5.0 with strict mode turned on I
would like to use RPAD however at times a NULL var will be sent to the
function. My goal is to have a function that will convert the NULL to a
blank string to get the result of EXAMPLE3 if a NULL is returned.
EXAMPLE 1
mysql select rpad(null,5,'1');
+--+
| rpad(null,5,'1') |
+--+
| NULL |
+--+
1 row in set (0.00 sec)
EXAMPLE 2
mysql select rpad('0',5,'1');
+-+
| rpad('0',5,'1') |
+-+
| 0 |
+-+
1 row in set (0.00 sec)
mysql
EXAMPLE 3
mysql select rpad('',5,'1');
++
| rpad('',5,'1') |
++
| 1 |
++
1 row in set (0.02 sec)
Is there a function I can use to convert the null string to a blank string
before it is sent to RPAD in a basic installation of MySQL or will I need
to create a function?
Wishing you the best you know you deserve,
__
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ 08405
Phone 609.485.5401
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max number of 64 indices per table?
Horst Jäger ha scritto: Hi everyone, the number of incices per table seems to be restricted to 64. Any way to change that? recompile with configure --with-max-indexes=128 I'm using MySQL 5.0.27 . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: www.innodb.com
Heikki Tuuri ha scritto: Bill, we are moving the DNS of innodb.com from Capnova to Oracle Corp. I can now view http://www.innodb.com through my ISP, Elisa. Does anyone still have problems accessing http://www.innodb.com? If you cannot see some web page, you can resort to Google's cache to view it. I hope that we will not get more disruption of service this weekend. Best regards, This is the answer from ibm nameservers i.e. none: ##- dig www.innodb.com @ns.almaden.ibm.com. ; DiG 9.3.2 www.innodb.com @ns.almaden.ibm.com. ; (1 server found) ;; global options: printcmd ;; Got answer: ;; -HEADER- opcode: QUERY, status: NOERROR, id: 33840 ;; flags: qr rd; QUERY: 1, ANSWER: 0, AUTHORITY: 13, ADDITIONAL: 0 ;; QUESTION SECTION: ;www.innodb.com.IN A ;; AUTHORITY SECTION: com.172552 IN NS i.gtld-servers.net. com.172552 IN NS j.gtld-servers.net. com.172552 IN NS k.gtld-servers.net. com.172552 IN NS l.gtld-servers.net. com.172552 IN NS m.gtld-servers.net. com.172552 IN NS a.gtld-servers.net. com.172552 IN NS b.gtld-servers.net. com.172552 IN NS c.gtld-servers.net. com.172552 IN NS d.gtld-servers.net. com.172552 IN NS e.gtld-servers.net. com.172552 IN NS f.gtld-servers.net. com.172552 IN NS g.gtld-servers.net. com.172552 IN NS h.gtld-servers.net. ;; Query time: 188 msec ;; SERVER: 198.4.83.35#53(198.4.83.35) ;; WHEN: Fri Nov 10 13:04:51 2006 ;; MSG SIZE rcvd: 256 ##- This is a query to the internet: ##- [EMAIL PROTECTED] dbdesigner 1 $ dig www.innodb.com ; DiG 9.3.2 www.innodb.com ;; global options: printcmd ;; Got answer: ;; -HEADER- opcode: QUERY, status: NOERROR, id: 3995 ;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0 ;; QUESTION SECTION: ;www.innodb.com.IN A ;; ANSWER SECTION: www.innodb.com. 80775 IN A 216.40.33.31 ;; Query time: 24 msec ;; SERVER: 192.168.4.1#53(192.168.4.1) ;; WHEN: Fri Nov 10 13:03:11 2006 ;; MSG SIZE rcvd: 48 [EMAIL PROTECTED] dbdesigner 0 $ dig -x 216.40.33.31 ; DiG 9.3.2 -x 216.40.33.31 ;; global options: printcmd ;; Got answer: ;; -HEADER- opcode: QUERY, status: NOERROR, id: 33913 ;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 3, ADDITIONAL: 1 ;; QUESTION SECTION: ;31.33.40.216.in-addr.arpa. IN PTR ;; ANSWER SECTION: 31.33.40.216.in-addr.arpa. 1200 IN PTR www.renewyourname.net. ;; AUTHORITY SECTION: 33.40.216.in-addr.arpa. 1200IN NS dns1.tucows.com. 33.40.216.in-addr.arpa. 1200IN NS dns2.tucows.com. 33.40.216.in-addr.arpa. 1200IN NS dns3.tucows.com. ;; ADDITIONAL SECTION: dns3.tucows.com.172051 IN A 204.50.180.59 ;; Query time: 263 msec ;; SERVER: 192.168.4.1#53(192.168.4.1) ;; WHEN: Fri Nov 10 13:03:19 2006 ;; MSG SIZE rcvd: 161 [EMAIL PROTECTED] dbdesigner 0 $ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
And another one is (in inverse order for laziness):
select
(8 1) AS `0`
, (8 2 1) AS `1`
, (8 4 1) AS `2`
, (8 8 1) AS `3`
, (8 16 1) AS `4`
, (8 32 1) AS `5`
, (8 64 1) AS `6`
, (8 128 1) AS `7`
;
Ed Reed wrote:
Well I solved the problem by using LPAD but it would be nice if there was a
more elegant way of handling this problem.
Thanks for the help.
Ed Reed [EMAIL PROTECTED] 1/10/06 2:52 PM
Thanks Gordon and Bill but this has one big problem
If my decimal number is 8 the result ends up,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 0 | 0 | | | | |
+---+---+---+---+---+---+---+---+
when what i really need is,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
+---+---+---+---+---+---+---+---+
Any thoughts?
Gordon Bruce [EMAIL PROTECTED] 1/10/06 1:44 PM
Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's.
Too much time looking at dump's.
-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql@lists.mysql.com
Subject: [SPAM] - Re: Converting decimal to binary - Bayesian Filter
detected spam
If you really do mean decimal (base 10) you could use Gordon's solution
like this:
SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
Hope this helps.
Gordon Bruce wrote:
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
- MID(CONV('A5',16,2),2,1) AS `6`,
- MID(CONV('A5',16,2),3,1) AS `5`,
- MID(CONV('A5',16,2),4,1) AS `4`,
- MID(CONV('A5',16,2),5,1) AS `3`,
- MID(CONV('A5',16,2),6,1) AS `2`,
- MID(CONV('A5',16,2),7,1) AS `1`,
- MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field
for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
Francesco Riosa wrote: And another one is (in inverse order for laziness): select (8 1) AS `0` , (8 2 1) AS `1` , (8 4 1) AS `2` , (8 8 1) AS `3` , (8 16 1) AS `4` , (8 32 1) AS `5` , (8 64 1) AS `6` , (8 128 1) AS `7` ; but this one looks better: select (8 1) AS `0` , (8 1 1) AS `1` , (8 2 1) AS `2` , (8 3 1) AS `3` , (8 4 1) AS `4` , (8 5 1) AS `5` , (8 6 1) AS `6` , (8 7 1) AS `7` ; http://dev.mysql.com/doc/refman/4.1/en/bit-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld threads
B. Heller wrote: Hello List, I compiled mySQLd 4.1.10a from Source on Linux 2.6. Although the server does it's work well, I wondered why there's only one mysqld thread running. Is that new/normal for mySQL 4.1? While using mySQL 3.23 I always had several threads running on my system. your glibc probably are compiled with NPTL. The thread are still there but to see them you must use a different sintax of ps , like: #ps -eLf -- No problem is so formidable that you can't walk away from it. ~ Charles M. Schulz But sometimes run fast is better ~ Francesco R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
