[Fwd: Case Insensitive Search]
Andrew Myers
When using TQL is it possible to do a case insensitive search?
eg I have:
select *
from
Recruitment.Application
join Recruitment.Status
join Recruitment.Applicant
join Recruitment.Position
where
Recruitment.Position.position_id = :positionId
and Recruitment.Applicant.first_name like :firstName
I am right to assume that I can't use
Upper(Recruitment.Applicant.first_name), because Upper() is a DB function?
Is there any workaround for this (that is a bit less painless than
creating a separate db column with the text in uppercase)?
Thanks,
Andrew.
mrad...@googlemail.com
your code should work fine.
Maybe silly question, but are you adding the percent signs in when
you're doing your setParam? e.g.
tquery.setParam( "firstName", "%"&arguments.firstName&"%", "string")>
Andrew
tSql. Without it (on Oracle 10g at least) search is case sensitive.
Will the upper() work in TSQL? I didn't try...
Mark Mandel
Mark
mrad...@googlemail.com
alter session set NLS_COMP=LINGUISTIC;
Andrew
What's the implications of doing this? Will it affect other SQL's
running on the same datasource?
On Jul 14, 7:32 pm, mradri...@googlemail.com wrote:
> What happens if you run this in its own cfquery prior to running the tql?
>
> alter session set NLS_COMP=LINGUISTIC;
>
>
>
>
> > Sounds like a good enhancement request...
>
> > W:www.compoundtheory.com
Mark Mandel
Mark
mrad...@googlemail.com
would make all SQL case insensitive on that datasource.
Andrew
I think that's going to be my solution.
At a guess how difficult will it be to implement the upper() function
in TQL? Is it something I could have a look at?
> W:www.compoundtheory.com
Andrew
thanks for the suggestion. :-)
Mark Mandel
way better, to be more flexible.
Feel free to have a look if you want.
Mark