Welcome to WebmasterWorld Guest from 54.197.66.254

Forum Moderators: open

Message Too Old, No Replies

MySQL Query Help Please

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

Junior Member

10+ Year Member

joined:Mar 16, 2004
posts: 58
votes: 0


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)

Senior Member from US 

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

joined:Dec 9, 2003
posts:3416
votes: 0


This should get you on the right track:

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

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5634
votes: 51


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 17, 2002
posts:1187
votes: 6


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)

Junior Member

10+ Year Member

joined:Mar 16, 2004
posts: 58
votes: 0


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.