homepage Welcome to WebmasterWorld Guest from 54.205.99.71
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL Query Help Please
Jessica97




msg:4303155
 11:41 pm on Apr 23, 2011 (gmt 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.

 

whoisgregg




msg:4305350
 1:21 pm on Apr 28, 2011 (gmt 0)

This should get you on the right track:

SELECT * FROM `table` WHERE `field` LIKE '%,'

rocknbil




msg:4305503
 5:17 pm on Apr 28, 2011 (gmt 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";

LifeinAsia




msg:4305509
 5:36 pm on Apr 28, 2011 (gmt 0)

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 '%,'

Frank_Rizzo




msg:4305546
 6:28 pm on Apr 28, 2011 (gmt 0)

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

Jessica97




msg:4305660
 11:06 pm on Apr 28, 2011 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved