|
Home > Archive > dBASE SQL Servers > December 2006 > sub string comparison in where clause
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
sub string comparison in where clause
|
|
| Claus Mygind 2006-12-12, 7:13 pm |
| As part of my selection cirteria I would like to include a substring
comparison of the last 16 characters in the field, but not sure how that can
be written
select * from t1 where right(col1, 16) = <myVariable>
how can the above example be re-written?
| |
| Roland Wingerter 2006-12-12, 7:13 pm |
| Claus Mygind wrote
> As part of my selection cirteria I would like to include a substring
> comparison of the last 16 characters in the field, but not sure how that
> can be written
>
> select * from t1 where right(col1, 16) = <myVariable>
>
> how can the above example be re-written?
-------
You can use the comparison operator LIKE and a wildcard like in the
following example.
open database dbasesamples
cVariable = "%fish"
select id, name from :dbasesamples:fish where name like :cVariable
Roland
| |
| Ivar B. Jessen 2006-12-12, 7:13 pm |
| On Tue, 12 Dec 2006 13:46:34 -0600, in dbase.sql-servers,
Subject: sub string comparison in where clause,
Message-ID: <yK1lteiHHHA.328@news-server>,
"Claus Mygind" <cmygind@tsccorp.com> wrote:
>As part of my selection cirteria I would like to include a substring
>comparison of the last 16 characters in the field, but not sure how that can
>be written
>
>select * from t1 where right(col1, 16) = <myVariable>
>
>how can the above example be re-written?
>
Read localSQL.hlp on 'SUBSTRING function' and then try this:
open database dbasesamples
select ID, Name from Fish where substring(Name from 14 for 16) = "flyfish"
list
Result:
Record# ID Name
4 4 Ornate Butterflyfish
Ivar B. Jessen
| |
| Lysander 2006-12-13, 5:20 am |
| Claus Mygind schrieb:
> As part of my selection cirteria I would like to include a substring
> comparison of the last 16 characters in the field, but not sure how that can
> be written
>
> select * from t1 where right(col1, 16) = <myVariable>
>
> how can the above example be re-written?
Roland's solution also works with MS-SQL server.
I have a dim memory saying that you were using MS-SQL.
For Ivar's solution, you must lookup the equivalent for the
"substring" function in MS-SQL help.
| |
| Claus Mygind 2006-12-13, 7:12 pm |
| Thank you all for the great advice.
I like Roland's suggestion and I don't know why I did not think about that
as I use that method in other locations.
But in the process I found that on MySQL the statement works as written
puts([ select * from t1 where right(col1, 16) = <myVariable> ])
I even tried adding trim( ) to the mix as I will have to increase the field
size in the future and it also worked fine like this
puts([ select * from t1 where right(trim(col1), 16) = <myVariable>])
but that is why I like Roland's suggestion because I don't have to worry
about the size of the field.
| |
| Roland Wingerter 2006-12-13, 7:12 pm |
| Claus Mygind wrote
> Thank you all for the great advice.
>
> I like Roland's suggestion and I don't know why I did not think about that
--------
Glad to help.
Roland
|
|
|
|
|