Welcome to WebmasterWorld Guest from 54.145.208.64

Forum Moderators: open

Message Too Old, No Replies

MySQL Query Help Please

   
11:41 pm on Apr 23, 2011 (gmt 0)

10+ Year Member



I'm needing to run a mysql query on a text field that will remove the last character of the field only if it is a comma

So for example, the field may consist of titles

Title 1, Title 2, Title 3
Title 1, Title 2, Title 3,

The first line without the comma at the end is fine, but if the last character in the field ends with a comma, like the 2nd example, I need to strip out the comma

Could anyone help me with the exact query that I would need to use to do this in mysql?

The table name is called Description and the field name is called orders

Thank you to anyone who could possibly help with this.
1:21 pm on Apr 28, 2011 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



This should get you on the right track:

SELECT * FROM `table` WHERE `field` LIKE '%,'
5:17 pm on Apr 28, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



This is one I wondered about for a very long time. You **should** be able to combine replace() with a regexp,

regexp ',$'

giving you something like

replace(column_name,regexp ',$', '')

but you can't. Regexps only match patterns in rows. The previous will error (and of course, point you to the manual, helpful as a stone.)

So it looks like you'll have to either create a stored procedure or a complicated select and sub select . . . or go the easier, get it done now way in programming. I was feeling rather Perl-y today so we're going "old school" - you'll have to convert this working example to the language of your choice. Perl being poetic as it is, it shouldn't be too hard to figure out (=~ is the substitution operator, equiv. of preg_replace.)


#!/usr/bin/perl
#
print "content-type:text/html\n\n";
use DBI;
#
$db_name = 'ENTER YOUR DB NAME';
$db_host = 'localhost'; # or remote host
$sql_login = ENTER YOUR DB USER';
$sql_pass = 'ENTER YOUR DB PASS';
$conn_string = "DBI:mysql:$db_name";
if ($db_host) { $conn_string .= ":$db_host"; }
$dbh = DBI->connect("$conn_string",$sql_login,$sql_pass) or print("cannot connect to database");
#
# this is an array = @ has a diff. meaning in Perl
@lines = (
'this is a line without a comma',
'this, line, has, inline, commas',
'Here\'s a comma,',
'this is one with spaces after it , ',
'here\'s another,',
'and another,',
'we don\'t need no steeking commas.'
);
#
$select = qq|create temporary table replace_test (id int(11) primary key auto_increment, txtval text);|;
$sth = $dbh->prepare("$select");
$rv = $sth->execute or print($dbh->errstr());
$sth->finish;
#
foreach $line (@lines) {
$line =~ s/'+/''/g; # this part is for the internal quotes
$select = qq|insert into replace_test (txtval) values ('$line');|;
$sth = $dbh->prepare("$select");
$rv = $sth->execute or print($dbh->errstr());
$sth->finish;
}
#
print "replacing:<br>\n";
$select = qq|select id,txtval from replace_test;|;
$sth = $dbh->prepare("$select");
$rv = $sth->execute or print($dbh->errstr());
while (($id,$val) = $sth->fetchrow_array) {
$val =~ s/,\s*$//g; # The actual removal of the end comma
$val =~ s/'+/''/g; # this part is for the internal quotes - nothing to do with the task
$select = qq|update replace_test set txtval='$val' where id=$id;|;
$sth2 = $dbh->prepare("$select");
$rv2 = $sth2->execute or print($dbh->errstr());
$sth2->finish;
}
$sth->finish;
#
print "Did it work?<br>\n";
#
$select = qq|select txtval from replace_test;|;
$sth = $dbh->prepare("$select");
$rv = $sth->execute or die($dbh->errstr());
while ($val = $sth->fetchrow_array) { print "$val<br>\n"; }
$sth->finish;
#
$select = qq|drop table replace_test;|;
$sth = $dbh->prepare("$select");
$rv = $sth->execute or print($dbh->errstr());
$sth->finish;
print "Done.<br>\n";
5:36 pm on Apr 28, 2011 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



The following is written for MS-SQL. I don't know MySQL, so the function may be a little different. Also, in MS SQL this will only work if the orders field is VARCHAR (substring doesn't work on TEXT fields).

UPDATE Description SET
orders=substring(orders,1,len(orders)-1)
WHERE orders LIKE '%,'
6:28 pm on Apr 28, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Not clear whether the OP wants to update a table to correct errors, or to retrieve data and display it correctly on the fly. To do the later this will work:

SELECT if(right(trim(ORDERS),1) = ',', substr(ORDERS, 1, length(ORDERS) -1) , ORDERS) from DESCRIPTION
11:06 pm on Apr 28, 2011 (gmt 0)

10+ Year Member



Thank you all for the great help on this query. I was wanting to update a table to fix the errors ie: commas at the end, but I think I will be able to take some of your examples and get this working.

You all are terrific...and really appreciate everyone who took the time to help me out with this.