from:"listsql listsql"

Split a Delimited String in SQL ( PROCEDURE split_string )

2006-06-21 Thread listsql listsql


Hi all,
I was trying this:
http://forge.mysql.com/snippets/view.php?id=4

That is supposed to emulate a  split()   in mysql.

Could anyone make it work ?

I've been trying without luck.  I 'm getting strange errors when trying to
create this procedure.

_
DROP PROCEDURE IF EXISTS split_string
 CREATE PROCEDURE split_string (
IN input TEXT,
IN delimiter VARCHAR(10)
 )
 SQL SECURITY INVOKER
 BEGIN
 DECLARE cur_position INT DEFAULT 1 ;
 DECLARE remainder TEXT;
 DECLARE cur_string VARCHAR(1000);
 DECLARE delimiter_length TINYINT UNSIGNED;
 DROP TEMPORARY TABLE IF EXISTS SplitValues;
 CREATE TEMPORARY TABLE SplitValues (
   value VARCHAR(1000) NOT NULL PRIMARY KEY
   ) ENGINE=MEMORY;
 SET remainder = input;
 SET delimiter_length = CHAR_LENGTH(delimiter);

  WHILE CHAR_LENGTH(remainder)  0 AND cur_position  0 DO
   SET cur_position = INSTR(remainder, delimiter);

 IF cur_position = 0 THEN
   SET cur_string = remainder;
ELSE
   SET cur_string = LEFT(remainder, cur_position - 1);
   END IF;

 IF TRIM(cur_string) != '' THEN
   INSERT INTO SplitValues VALUES (cur_string);
END IF;

 SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);

 END WHILE;
 END

__--

Best regards,
MARTIN

Re: Split a Delimited String in SQL ( PROCEDURE split_string )

2006-06-21 Thread listsql listsql


Sorry forgot to copy my version:

It's not acepting the delimiter command, can be ? I'm really missing
something here.
+---+
| version() |
+---+
| 4.0.24_Debian-10ubuntu2.3-log |
+---+
mysql delimiter //
   - CREATE PROCEDURE simpleproc (OUT param1 INT)
   - BEGIN
   -
   - SELECT COUNT(*) INTO param1 FROM t;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN


On 6/21/06, listsql listsql [EMAIL PROTECTED] wrote:


Hi all,
I was trying this:
http://forge.mysql.com/snippets/view.php?id=4

That is supposed to emulate a  split()   in mysql.

Could anyone make it work ?

I've been trying without luck.  I 'm getting strange errors when trying to
create this procedure.

_
DROP PROCEDURE IF EXISTS split_string
  CREATE PROCEDURE split_string (
 IN input TEXT,
 IN delimiter VARCHAR(10)
  )
  SQL SECURITY INVOKER
  BEGIN
  DECLARE cur_position INT DEFAULT 1 ;
  DECLARE remainder TEXT;
  DECLARE cur_string VARCHAR(1000);
  DECLARE delimiter_length TINYINT UNSIGNED;
  DROP TEMPORARY TABLE IF EXISTS SplitValues;
  CREATE TEMPORARY TABLE SplitValues (
value VARCHAR(1000) NOT NULL PRIMARY KEY
) ENGINE=MEMORY;
  SET remainder = input;
  SET delimiter_length = CHAR_LENGTH(delimiter);

   WHILE CHAR_LENGTH(remainder)  0 AND cur_position  0 DO
SET cur_position = INSTR(remainder, delimiter);

  IF cur_position = 0 THEN
SET cur_string = remainder;
 ELSE
SET cur_string = LEFT(remainder, cur_position - 1);
END IF;

  IF TRIM(cur_string) != '' THEN
INSERT INTO SplitValues VALUES (cur_string);
 END IF;

  SET remainder = SUBSTRING(remainder, cur_position +
delimiter_length);

  END WHILE;
  END

__--

Best regards,
MARTIN

Re: Split a Delimited String in SQL ( PROCEDURE split_string )

2006-06-21 Thread listsql listsql


Definitivaly stopping and going for one or two cups of coffee.
I was logged in to another mysql... when I copied the last email. Sorry
guys, I don't want to add extra non-sense text to the list!
+--+
| version()|
+--+
| 5.0.22-Debian_1.dotdeb.1-log |
+--+

Now delimiter works fine, but I still get error trying to create the
procedure ( http://forge.mysql.com/snippets/view.php?id=4 ):

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'CREATE PROCEDURE split_string (
IN input TEXT,
IN delimiter VARCHAR(10' at line 2


Just for the happy comment: Argentina Will win the match tonight against
Holland :)
MARTIN


On 6/21/06, listsql listsql [EMAIL PROTECTED] wrote:


Sorry forgot to copy my version:

It's not acepting the delimiter command, can be ? I'm really missing
something here.
+---+
| version() |
+---+
| 4.0.24_Debian-10ubuntu2.3-log |
+---+
mysql delimiter //
- CREATE PROCEDURE simpleproc (OUT param1 INT)
- BEGIN
-
- SELECT COUNT(*) INTO param1 FROM t;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN



On 6/21/06, listsql listsql [EMAIL PROTECTED] wrote:

 Hi all,
 I was trying this:
 http://forge.mysql.com/snippets/view.php?id=4

 That is supposed to emulate a  split()   in mysql.

 Could anyone make it work ?

 I've been trying without luck.  I 'm getting strange errors when trying
 to create this procedure.

 _
 DROP PROCEDURE IF EXISTS split_string
   CREATE PROCEDURE split_string (
  IN input TEXT,
  IN delimiter VARCHAR(10)
   )
   SQL SECURITY INVOKER
   BEGIN
   DECLARE cur_position INT DEFAULT 1 ;
   DECLARE remainder TEXT;
   DECLARE cur_string VARCHAR(1000);
   DECLARE delimiter_length TINYINT UNSIGNED;
   DROP TEMPORARY TABLE IF EXISTS SplitValues;
   CREATE TEMPORARY TABLE SplitValues (
 value VARCHAR(1000) NOT NULL PRIMARY KEY
 ) ENGINE=MEMORY;
   SET remainder = input;
   SET delimiter_length = CHAR_LENGTH(delimiter);

WHILE CHAR_LENGTH(remainder)  0 AND cur_position  0 DO
 SET cur_position = INSTR(remainder, delimiter);

   IF cur_position = 0 THEN
 SET cur_string = remainder;
  ELSE
 SET cur_string = LEFT(remainder, cur_position - 1);
 END IF;

   IF TRIM(cur_string) != '' THEN
 INSERT INTO SplitValues VALUES (cur_string);
  END IF;

   SET remainder = SUBSTRING(remainder, cur_position +
 delimiter_length);

   END WHILE;
   END

 __--

 Best regards,
 MARTIN

Social Networking querys

2005-02-02 Thread listsql listsql

Since I read about Foaf [ http://www.foaf-project.org/ ], I become
interested with Social Networking, and readed a lot about it.
I 've been playing as well with mysql and join querys to represent
network's of people. As I made some queries in google and didn't came
with a lot interesting info about this, now I will comment here my own
querys.

urelation table:  (this kind of relation is called the adjacency list
model, or recursion)
+-+-+
| uid | fid |
+-+-+
|   1 |   2 |
|   2 |   3 |
|   1 |   3 |
|   3 |   2 |
|   3 |   0 |
|   0 |   2 |
|   3 |   1 |
+-+-+
This represent's the id of  people and the id of their friend ('s)

uprofile table: 
+-+---+
| uid | name  |
+-+---+
|   0 | martin 0  |
|   1 | pedro 1   |
|   2 | pablo 2   |
|   3 | matias 3  |
|   4 | mateo 4   |
|   5 | claudio 5 |
+-+---+



So if I want to get the friend's and friend's of friend's  of  pablo:

SELECT p1.name  p1,p2.name  p2
FROM uprofile p1,uprofile p2
left join urelation r1 ON r1.fid=p1.uid 
and r1.uid =2 
left join urelation r2 ON r2.fid=p2.uid 
where r2.uid =r1.fid 

+--+--+
| p1   | p2   |
+--+--+
| matias 3 | martin 0 |
| matias 3 | pedro 1  |
| matias 3 | pablo 2  |
+--+--+

And I add logically one join more if I want to get deeper in the network.
The obvious problem is that for the first table p1 I will get the
repeating Id, but that is not an issue now.

Where I wanted to get more feedback is, there is some method to
iterate in this relation to avoid joining the table in itself each
time ?
Or also:
Is this the best way to store human-like social relations, or there is
a better way to do this ?

Thanks in advance,
-- 
Martin
 (Welcome to saving hierarchical data in mysql: Recursion until your
head explodes)

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

Re: Social Networking querys

2005-02-02 Thread listsql listsql

i'm not sure who the original poster was
I'm Fasani, I work in a spanish telecommunications company on the
statistics department as a DBA/developer. I'm on this list because we
use mysql for the online statistics and I'm always reading this when I
have some free time.

Thanks all who commented on this, I 'm researching now because I'm
making something different that I want to build on, it's kind of
different from friendster.
I know that I will face several problems in making what I want to
achieve. But mysql is powerfull enough to be the DB engine.

I hope that this not become off topic, I will search more on link
analisys and social networking, if there is any other thing related
that is not regarding mysql please send me to my personal email and
not to all the list.
Best regards,
--
Martin F.
www.movil.be

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

Emulating timediff in 4.0 ?

2004-11-09 Thread listsql listsql

TIMEDIFF(expr,expr2)
TIMEDIFF() returns the time between the start time expr and the
end time expr2. expr and expr2 are time or date-and-time expressions,
but both must be of the same type.

mysql SELECT TIMEDIFF('1997-12-31 23:59:59.01',
- '1997-12-30 01:01:01.02');
- '46:58:57.99'


Any fast tip in how to emulate this on mysql 4.0 ?
May save me hours of bitching :/

Thanks in advance.

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

Re: Emulating timediff in 4.0 ?

2004-11-09 Thread listsql listsql

Thanks Eamon,
I was trying with something like:
SELECT  stamp,now()+0,  if ((now() - stamp)6000, (now() - stamp)
/60,0) as waitminutes from smstablademotaxi


 //I'm interested to get it only for the last 60 minutes
result:
+++-+
| stamp  | now()+0| waitminutes |
+++-+
| 20041109180348 | 20041109180455 |1.78 |
| 20041109180350 | 20041109180455 |1.75 |
+++-+

Thanks for the feedback :)

On Tue, 9 Nov 2004 10:20:31 -0600, Eamon Daly [EMAIL PROTECTED] wrote:
 A combination of UNIX_TIMESTAMP and SEC_TO_TIME should do
 it:
 
 SELECT
 UNIX_TIMESTAMP('1997-12-31 23:59:59.01') as `date 1 in seconds`,
 UNIX_TIMESTAMP('1997-12-30 01:01:01.02') as `date 2 in seconds`,
 UNIX_TIMESTAMP('1997-12-31 23:59:59.01') -
   UNIX_TIMESTAMP('1997-12-30 01:01:01.02') as `diff in seconds`,
 SEC_TO_TIME(
   UNIX_TIMESTAMP('1997-12-31 23:59:59.01') -
   UNIX_TIMESTAMP('1997-12-30 01:01:01.02')) as `diff in hh:mm:ss`
 
 date 1 in seconds: 883634399
 date 2 in seconds: 883465261
   diff in seconds: 169138
  diff in hh:mm:ss: 46:58:58
 
 This throws away the msecs, though. Also, negative diffs
 work, surprisingly:
 
 SELECT
 UNIX_TIMESTAMP('1997-12-30 01:01:01.02') as `date 1 in seconds`,
 UNIX_TIMESTAMP('1997-12-31 23:59:59.01') as `date 2 in seconds`,
 UNIX_TIMESTAMP('1997-12-30 01:01:01.02') -
   UNIX_TIMESTAMP('1997-12-31 23:59:59.01') as `diff in seconds`,
 SEC_TO_TIME(
   UNIX_TIMESTAMP('1997-12-30 01:01:01.02') -
   UNIX_TIMESTAMP('1997-12-31 23:59:59.01')) as `diff in hh:mm:ss`
 
 date 1 in seconds: 883465261
 date 2 in seconds: 883634399
   diff in seconds: -169138
  diff in hh:mm:ss: -46:58:58
 
 So you've got that going for you.
 
 
 Eamon Daly
 
 
 
 
 - Original Message -
 From: listsql listsql [EMAIL PROTECTED]
 To: mysql [EMAIL PROTECTED]
 Sent: Tuesday, November 09, 2004 7:11 AM
 Subject: Emulating timediff in 4.0 ?
 
  TIMEDIFF(expr,expr2)
 TIMEDIFF() returns the time between the start time expr and the
  end time expr2. expr and expr2 are time or date-and-time expressions,
  but both must be of the same type.
 
  mysql SELECT TIMEDIFF('1997-12-31 23:59:59.01',
 - '1997-12-30 01:01:01.02');
 - '46:58:57.99'
 
 
  Any fast tip in how to emulate this on mysql 4.0 ?
  May save me hours of bitching :/
 
  Thanks in advance.
 


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

Split a Delimited String in SQL ( PROCEDURE split_string )

Re: Split a Delimited String in SQL ( PROCEDURE split_string )

Re: Split a Delimited String in SQL ( PROCEDURE split_string )

Social Networking querys

Re: Social Networking querys

Emulating timediff in 4.0 ?

Re: Emulating timediff in 4.0 ?

7 matches

Site Navigation

Mail list logo

Footer information