I have a list like this:
Reckless Engineer
St Bonaventures
The Blue Mountain
The Croft
The Cube Club
The Folk house
I'd like to reorder that ignoring the leading "The".
How do I do that?
Jeff
8  8614 
Jeff wrote:
I have a list like this:
Reckless Engineer
St Bonaventures
The Blue Mountain
The Croft
The Cube Club
The Folk house
I'd like to reorder that ignoring the leading "The".
How do I do that?
Jeff
This should work
SELECT *
FROM table1
ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );
"Jeff" <do*********@all.ukwrote in message
news:Jq*****************@newsread3.news.pas.earthl ink.net...
I have a list like this:
Reckless Engineer
St Bonaventures
The Blue Mountain
The Croft
The Cube Club
The Folk house
I'd like to reorder that ignoring the leading "The".
How do I do that?
Jeff
Here is something I posted a while back. It will work for you if you
understand it.
CREATE TABLE Titles(
Title varchar(100));
INSERT INTO TITLES VALUES('The World According to
Garp'),('Casablanca'),('The Day After Tomorrow');
SELECT
CASE
WHEN SUBSTRING(Title,1,4) = 'The '
THEN CONCAT(SUBSTRING(Title,5,LENGTH(Title) - 4),',The')
ELSE Title
END as T
FROM
Titles
ORDER BY T ASC
<za*******@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
Jeff wrote:
I have a list like this:
Reckless Engineer
St Bonaventures
The Blue Mountain
The Croft
The Cube Club
The Folk house
I'd like to reorder that ignoring the leading "The".
How do I do that?
Jeff
This should work
SELECT *
FROM table1
ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );
How can this possibly work?
Rich
Rich Ryan wrote:
>ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );
How can this possibly work?
The first problem I see with this is that it replaces _all_ occurrences
of "the ", not just occurrences of the whole word "the" at the beginning
of the string.
Regards,
Bill K.
Rich Ryan wrote:
<za*******@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
Jeff wrote:
I have a list like this:
>
Reckless Engineer
St Bonaventures
The Blue Mountain
The Croft
The Cube Club
The Folk house
>
I'd like to reorder that ignoring the leading "The".
>
How do I do that?
>
Jeff
This should work
SELECT *
FROM table1
ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );
How can this possibly work?
Rich
eh? but it DOES work !?! - well, up to a point
strawberry wrote:
Rich Ryan wrote:
>><za*******@gmail.comwrote in message
news:11**********************@h48g2000cwc.google groups.com...
>>>Jeff wrote:
I have a list like this:
Reckless Engineer
St Bonaventures
The Blue Mountain
The Croft
The Cube Club
The Folk house
I'd like to reorder that ignoring the leading "The".
How do I do that?
Jeff
This should work
SELECT *
FROM table1
ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );
How can this possibly work?
Rich
eh? but it DOES work !?! - well, up to a point
Satisfied my needs, and if there was a "the" elsewhere, the ordering
couldn't be far off.
Is it possible to either sub in my own function or use something more
regex like? I can think of lots of times when I'd want to lead the
returns with a certain item.
Jeff
>
Rich Ryan wrote:
"Jeff" <do*********@all.ukwrote in message
news:Jq*****************@newsread3.news.pas.earthl ink.net...
> I have a list like this:
Reckless Engineer
St Bonaventures
The Blue Mountain
The Croft
The Cube Club
The Folk house
I'd like to reorder that ignoring the leading "The".
How do I do that?
Jeff
Here is something I posted a while back. It will work for you if you
understand it.
CREATE TABLE Titles(
Title varchar(100));
INSERT INTO TITLES VALUES('The World According to
Garp'),('Casablanca'),('The Day After Tomorrow');
SELECT
CASE
WHEN SUBSTRING(Title,1,4) = 'The '
THEN CONCAT(SUBSTRING(Title,5,LENGTH(Title) - 4),',The')
ELSE Title
END as T
FROM
Titles
ORDER BY T ASC
Thank, I had no idea that CASE existed.
<URL: http://dev.mysql.com/doc/refman/5.0/...functions.html />
I stumbled across this fascinating bit also, while searching:
<URL: http://dev.mysql.com/doc/mysql/searc...s.html&lang=en
/>
<sample>
+apple +(>turnover <strudel)
Find rows that contain the words ``apple'' and ``turnover'', or
``apple'' and ``strudel'' (in any order), but rank ``apple turnover''
higher than ``apple strudel''.
</sample>
Is that a part of MySQL standard SQL commands, or is that for FULL TEXT
searches only?
Jeff wrote:
<sample>
+apple +(>turnover <strudel)
Find rows that contain the words ``apple'' and ``turnover'', or
``apple'' and ``strudel'' (in any order), but rank ``apple turnover''
higher than ``apple strudel''.
</sample>
Is that a part of MySQL standard SQL commands, or is that for FULL TEXT
searches only?
It's part of FULL TEXT searching. http://dev.mysql.com/doc/refman/5.0/...t-boolean.html
That is part of MySQL standard.
Regards,
Bill K. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tim Conner |
last post by:
Is there a way to write a faster function ?
public static bool IsNumber( char Value )
{
if (Regex.IsMatch( Value.ToString(), @"^+$" ))
{
return true;
}
else return false;
}
|
by: Ryan Taylor |
last post by:
Hello.
I am trying to create a regular expression that will let me know if a string
has the following criteria. Order does not matter in the string, but when
building a regular expression it...
|
by: Daniel R. Rossnagel |
last post by:
I can obtain Where clauses, order by, group by, of a consultation SQL, by
means of some class similar to codemodel
|
by: Extremest |
last post by:
I have a huge regex setup going on. If I don't do each one by itself
instead of all in one it won't work for. Also would like to know if
there is a faster way tried to use string.replace with all...
|
by: Extremest |
last post by:
I am using this regex.
static Regex paranthesis = new Regex("(\\d*/\\d*)",
RegexOptions.IgnoreCase);
it should find everything between parenthesis that have some numbers
onyl then a forward...
|
by: Andy Mabbett |
last post by:
I have an HTML document which has a long table, with five columns.
I want to swap the order of columns four and five, without breaking
(indeed, without otherwise altering) the valid markup.
Is...
|
by: Greg Collins [Microsoft MVP] |
last post by:
I've done a bit of research on this topic in the newsgroups and on MSDN, and though it sounds possible, I still don't understand how I would make it work.
I want to be able to use named groups to...
|
by: aspineux |
last post by:
My goal is to write a parser for these imaginary string from the SMTP
protocol, regarding RFC 821 and 1869.
I'm a little flexible with the BNF from these RFC :-)
Any comment ?
tests=
def...
|
by: morleyc |
last post by:
Hi, i would like to remove a number of characters from my string (\t
\r \n which are throughout the string), i know regex can do this but i
have no idea how. Any pointers much appreciated.
Chris
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
