Robyn,
> Could anyone please tell me how I write an SQL statement to seperate
> text from one field into multiple fields based on a aprticular
character
> (i.e. tab/comma/space). For example, I have a text field containing:
> Text=Robyn Bailey Brisbane
I understand you have all of this in just ONE text field (that's not a
good idea, though).
You could use the MySQL string functions to separate substrings, but
that's not a good idea, either, because SQL is not designed for tasks
like this.
You should rather use your favourite programming language (mine is PHP
:) and do something like this (the example is with PHP, but it will work
the same way in PERL etc.):
$sql = "SELECT mytext FROM myentries";
$res = mysql_query($sql);
$i = 0;
while($row = mysql_fetch_array($res)) {
$arr[$i] = explode('=', $row[0]);
$i++;
}
When the while loop ends, you have the split values in $arr, and you can
cycle through $arr to create your report.
Note that this is untested code, just to give you an idea what to do.
BTW: After splitting up "mytext" into pieces ($arr) you should consider
inserting the split values into a new table with a better table design.
Regards,
--
Stefan Hinz <[EMAIL PROTECTED]>
Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
----- Original Message -----
From: "Robyn Bailey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, January 20, 2003 2:08 AM
Subject: FW: Text field question
> Could anyone please tell me how I write an SQL statement to seperate
> text from one field into multiple fields based on a aprticular
character
> (i.e. tab/comma/space). For example, I have a text field containing:
> Text=Robyn Bailey Brisbane
>
> I want to seperate this (per query) into 3 fields so that I can sort
and
> count the fields: Name=Robyn Surname=Bailey Location=Brisbane
>
> I dont want it permanently just per query (for a report).
>
> Thanks in advance
> Robyn Bailey, CISSP
>
>
>
> This email and any attachments are subject to copyright. They may also
> contain confidential information. This email and any attachments may
not
> be distributed, reproduced, copied, stored or transmitted in any form
or
> by any means, without the prior written consent of Bridge Point
> Communications Pty Ltd ABN 29 083 424 668. Any personal information in
> this email must be handled in accordance with the Privacy Act 1988
> (Cth). Emails may be interfered with, may contain computer viruses or
> other defects and may not be successfully replicated on other systems.
> Bridge Point Communications Pty Ltd gives no warranties in relation to
> these matters. If you have any doubts about the authenticity of an
email
> purportedly sent by us, please contact Bridge Point Communications Pty
> Ltd immediately.
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
