Embedded table in sql string?

#11

On Feb 24, 2:25*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

Quote:

On Feb 23, 10:35*pm, SpreadTooThin <bjobrie... (AT) gmail (DOT) com> wrote:

as well as ones like "Smack" & "Bob" & "Rock"

Sometime people take analogies toooo far...

I was pointing out that your LIKE arguments would produce names were
those letters appear anywhere in the name, not only where they are at
the beginning as your post seemed to suggest.

True enough... It's tough sometimes to make a posting that describes
sorta what your doing when you can't describe exactly what you are
doing, without distracting from the actual technical question your
posing...

#12

Jerry Stuckle wrote:

Quote:

On 2/23/2011 7:39 PM, Norman Peelman wrote:
SpreadTooThin wrote:
I have an sql statement that needs to be executed of a list of
parameters.

say for example I have a table of Clients [LastName, Nationality]

SELECT LastName, Nationality FROM Clients WHERE LastName LIKE %Mc%

So this gives me a list of all people like McNeil, McKnight and their
nationality.

but what if I wanted to do this for another name like Mac or OB or OC

Can I put a list of 'likes' in the SQL statement?

SELECT LastName, Nationality FROM Clients WHERE LastName LIKE (%Mc%,
%Mac%, %OB%, %OC%) ORDER BY LastName

and order them by the Mc, Mac, OB, OC parameter...

What does the syntax look like for this if it can be done?

SELECT LastName, Nationality FROM Clients WHERE
LastName LIKE '%Mc%' OR
LastName LIKE '%Mac%' OR
LastName LIKE '%OB%' OR
LastName LIKE '%OC%'
ORDER BY LastName ASC

If your programming/scripting language has support for it, you can store
a soundex of the name and search by that.

Which does not provide the sort order the op requests.

Luuk's response, while a bit more complicated, is also correct.

Ooops... I did overlook the order by parameter condition.

SELECT LastName, Nationality FROM Clients WHERE
LastNameSoundex LIKE 'M%'
ORDER BY LastName ASC

would yield all the lastnames starting with 'M'.

$lns = soundex($last_name);
SELECT LastName, Nationality FROM Clients WHERE
LastNameSoundex LIKE '$lns'
ORDER BY LastName ASC

would yield all the last names that sounded alike and even catches a
lot of misspellings.

This is a PHP example but I know other languages have access to
soundex as well. I only mention this because soundex can give you a lot
of power when dealing with names (words) in a database.

--
Norman
Registered Linux user #461062
-Have you been to www.mysql.com yet?-

#13

On Feb 24, 6:06*pm, Norman Peelman <npeel... (AT) cfl (DOT) rr.com> wrote:

Quote:

Jerry Stuckle wrote:
On 2/23/2011 7:39 PM, Norman Peelman wrote:
SpreadTooThin wrote:
I have an sql statement that needs to be executed of a list of
parameters.

say for example I have a table of Clients [LastName, Nationality]

SELECT LastName, Nationality FROM Clients WHERE LastName LIKE %Mc%

So this gives me a list of all people like McNeil, McKnight and their
nationality.

but what if I wanted to do this for another name like Mac or OB or OC

Can I put a list of 'likes' in the SQL statement?

SELECT LastName, Nationality FROM Clients WHERE LastName LIKE (%Mc%,
%Mac%, %OB%, %OC%) ORDER BY LastName

and order them by the Mc, Mac, OB, OC parameter...

What does the syntax look like for this if it can be done?

SELECT LastName, Nationality FROM Clients WHERE
LastName LIKE '%Mc%' OR
LastName LIKE '%Mac%' OR
LastName LIKE '%OB%' OR
LastName LIKE '%OC%'
ORDER BY LastName ASC

If your programming/scripting language has support for it, you can store
a soundex of the name and search by that.

Which does not provide the sort order the op requests.

Luuk's response, while a bit more complicated, is also correct.

Ooops... I did overlook the order by parameter condition.

* *SELECT LastName, Nationality FROM Clients WHERE
* * * * LastNameSoundex LIKE 'M%'
* * * * ORDER BY LastName ASC

would yield all the lastnames starting with 'M'.

* *$lns = soundex($last_name);
* *SELECT LastName, Nationality FROM Clients WHERE
* * * * LastNameSoundex LIKE '$lns'
* * * * ORDER BY LastName ASC

* would yield all the last names that sounded alike and even catches a
lot of misspellings.

* This is a PHP example but I know other languages have access to
soundex as well. I only mention this because soundex can give you a lot
of power when dealing with names (words) in a database.

--
Norman
Registered Linux user #461062
-Have you been towww.mysql.comyet?-

The soundex would be quite useful to me because people are always
making spelling mistakes in my database...
$lns = soundex($last_name);
LastNameSoundex LIKE '$lns'

I guessing soundex is implemented in mysql community server.
I don't understand what LastNameSoundex is? Is it a column in the
table that is different from LastName?

#14

SpreadTooThin wrote:

Quote:

On Feb 24, 6:06 pm, Norman Peelman <npeel... (AT) cfl (DOT) rr.com> wrote:
Jerry Stuckle wrote:
On 2/23/2011 7:39 PM, Norman Peelman wrote:
SpreadTooThin wrote:
I have an sql statement that needs to be executed of a list of
parameters.
say for example I have a table of Clients [LastName, Nationality]
SELECT LastName, Nationality FROM Clients WHERE LastName LIKE %Mc%
So this gives me a list of all people like McNeil, McKnight and their
nationality.
but what if I wanted to do this for another name like Mac or OB or OC
Can I put a list of 'likes' in the SQL statement?
SELECT LastName, Nationality FROM Clients WHERE LastName LIKE (%Mc%,
%Mac%, %OB%, %OC%) ORDER BY LastName
and order them by the Mc, Mac, OB, OC parameter...
What does the syntax look like for this if it can be done?
SELECT LastName, Nationality FROM Clients WHERE
LastName LIKE '%Mc%' OR
LastName LIKE '%Mac%' OR
LastName LIKE '%OB%' OR
LastName LIKE '%OC%'
ORDER BY LastName ASC
If your programming/scripting language has support for it, you can store
a soundex of the name and search by that.
Which does not provide the sort order the op requests.
Luuk's response, while a bit more complicated, is also correct.
Ooops... I did overlook the order by parameter condition.

SELECT LastName, Nationality FROM Clients WHERE
LastNameSoundex LIKE 'M%'
ORDER BY LastName ASC

would yield all the lastnames starting with 'M'.

$lns = soundex($last_name);
SELECT LastName, Nationality FROM Clients WHERE
LastNameSoundex LIKE '$lns'
ORDER BY LastName ASC

would yield all the last names that sounded alike and even catches a
lot of misspellings.

....actually LastNameSoundex = '$lns' would be better.

Quote:

This is a PHP example but I know other languages have access to
soundex as well. I only mention this because soundex can give you a lot
of power when dealing with names (words) in a database.

--
Norman
Registered Linux user #461062
-Have you been to www.mysql.com yet?-

The soundex would be quite useful to me because people are always
making spelling mistakes in my database...
$lns = soundex($last_name);
LastNameSoundex LIKE '$lns'

I guessing soundex is implemented in mysql community server.
I don't understand what LastNameSoundex is? Is it a column in the
table that is different from LastName?

Depending on how you do it, yes it is an additional column in the
database. You can compute the soundex and store it in the database and
even index it. Then you compute the soundex (input) prior to doing the
search.

Or you can use the MySQL function below (which I have not, yet) but I
do not know what the performance hit is.

http://dev.mysql.com/doc/refman/5.0/...nction_soundex

PHP soundex() examples:

Name: Soundex:
----- --------
Smith S530
Schmidt S530

Johnson J525
Johannson J525

Peelman P455
Pelman P455

--
Norman
Registered Linux user #461062
-Have you been to www.mysql.com yet?-

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Embedded table in sql string?

comp.databases.mysql comp.databases.mysql

Re: Embedded table in sql string? - 02-24-2011 , 10:56 AM

Re: Embedded table in sql string? - 02-24-2011 , 07:06 PM

Re: Embedded table in sql string? - 02-25-2011 , 01:00 PM

Re: Embedded table in sql string? - 02-25-2011 , 06:32 PM