SQL Query to search for "non letter"

Discussion forum for MOD Writers regarding MOD Development.
OBeer-WAN-Kenobi
Registered User
Posts: 38
Joined: Fri Jul 25, 2008 2:22 am

Re: SQL Query to search for "non letter"

Post by OBeer-WAN-Kenobi » Fri Aug 15, 2008 8:45 pm

Ok, just to be clear........
Will this work?

Code: Select all

$sql = 'SELECT topic_title, forum_id, topic_id
   FROM ' . TOPICS_TABLE . '
   WHERE topic_approved = 1 
      AND forum_id = 1
      AND topic_moved_id = 0
      AND topic_title ucase(substring(topic_title,1,1)) NOT BETWEEN 'A' AND 'Z'
      ' . $sql_where . '
   ORDER BY topic_title ASC';
I'm not very good with this. I program CNC Machine tools not computer code! LOL. I just copied this code from another thing and am trying to adapt it to my application; with pretty good luck so far, I might add. ;)
Image

OBeer-WAN-Kenobi
Registered User
Posts: 38
Joined: Fri Jul 25, 2008 2:22 am

Re: SQL Query to search for "non letter"

Post by OBeer-WAN-Kenobi » Fri Aug 15, 2008 10:56 pm

Whoa... wait a sec...
What about this?

Code: Select all

$sql = 'SELECT topic_title, forum_id, topic_id
	FROM ' . TOPICS_TABLE . '
	WHERE topic_approved = 1 
		AND forum_id = 1
		AND topic_moved_id = 0
		AND topic_title NOT BETWEEN "A" AND "Z"
		' . $sql_where . '
	ORDER BY topic_title ASC';

$result = $db->sql_query($sql);
I just took out the percent signs.
It seems to be doing what I want now. What do you think?
Image

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: SQL Query to search for "non letter"

Post by drathbun » Sat Aug 16, 2008 2:12 am

When you do a between operation using 'A' and 'Z' then you compare the entire topic title with just one character. So "ZZ" comes after "Z" in that regard.

What I posted earlier would work.

Code: Select all

where ucase(substring(topic_title,1,1)) NOT BETWEEN 'A' AND 'Z'
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

OBeer-WAN-Kenobi
Registered User
Posts: 38
Joined: Fri Jul 25, 2008 2:22 am

Re: SQL Query to search for "non letter"

Post by OBeer-WAN-Kenobi » Sat Aug 16, 2008 3:05 am

Thanks again drathbun,

I had a little trouble but got it working with your code:

This:

Code: Select all

$sql = 'SELECT topic_title, forum_id, topic_id
	FROM ' . TOPICS_TABLE . '
	WHERE topic_approved = 1 
		AND forum_id = 1
		AND topic_moved_id = 0
		AND ucase(substring(topic_title,1,1)) NOT BETWEEN "A" AND "Z"
		' . $sql_where . '
	ORDER BY topic_title ASC';

$result = $db->sql_query($sql);
Seems to be working correctly.... of course I've thought that before! :oops:

Thanks!
Image

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: SQL Query to search for "non letter"

Post by drathbun » Sat Aug 16, 2008 3:29 pm

It looks correct now :)
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

Locked

Return to “[3.0.x] MOD Writers Discussion”