Re: SQL Question???
Hi all,
What's wrong with:
select * from tab1 where column1[4,5] =3D "87"
Simple :-)
I'll admit I've never heard of "mod", but the above
solution works fine for me!
Cheers,
Richard.
----------------------------------------------------------------- =20
| _ =AF\\ | Richard Thomas |
| \\ 0_ | r.thomas@csl.gov.uk |
| \\ / | |
| oo/ | "20 Regal and a four-pack, |
| / \\ | I guess I'm set for the night" |
| \\_/ | - "TV Tan" The Wildhearts |
-----------------------------------------------------------------
} From ilist@rmy.emory.edu Thu Oct 19 03:33:43 1995
} From: proberts@lynx.informix.com (Paul Roberts)
} Subject: Re: SQL Question???
} Date: 18 Oct 1995 21:30:10 GMT
} To: informix-list@rmy.emory.edu
} X-Informix-List-To: rt10hp@csl.gov.uk
} X-Informix-List-Id: <news.18044>
}=20
} In article <463ihb$q72@ixnews7.ix.netcom.com>,
} Mark Truty <marktrut@ix.netcom.com> wrote:
} >
} >I am trying to do a SQL query on a numeric field.
} >How do I query the last 2 numbers of a 5 digit field?
} >Example: number field =3D 54387 - I want to search for all records =
that
} >end in 87.
} >Thanks
}=20
}=20
} I really feel that we ought to be able to say:
}=20
} select *
} from tab1
} where column1 mod 100 =3D 87
}=20
} But last time I looked, the "mod" function was available in 4GL but=20
} not in SQL.
}=20
} Here's a horribly clunky solution (which I present only in order to
} encourage others to present their far more elegant solutions. That's
} my story and I'm sticking to it):
}=20
} select tab1.*, "xxxxxxxx" dummy_field
} from tab1
} into temp t1 with no log ;
}=20
} update t1 set dummy_field =3D column1 ;
}=20
} [Or explicitly create a temp table "t1" with a character=20
} column "dummy_field" in place of tab1's integer column=20
} "column1" and then:
}=20
} insert into t1 select * from tab1
}=20
} I hate typing create table statements, so I do it as =
above]
}=20
} select * from t1 where dummy_field matches "*87"
}=20
} - Paul
}=20