Problem in Query

[prriya pratheep]

Hi friends,

I want to do one jdbc program which runs the query with wild characters.

Before that in my table filed values are like "ARVOD TEC ", that is a lot of space at the end.

In jsp page if the user enter AR* it should display all the data starts with AR
if the user enter *VOD it should display all the data ends with vod.

I wrote the code to check if the * is at last.In this code strmpn contains is field value

for (int i=0; i < strmpn.length(); i++)
if (strmpn.charAt(i)=='*')
{
flag = true;
}
String replaces =strmpn.replace('*','%');
if (flag)
Query = Query +" MPN like '"+replaces +"'";
else
Query = Query +" MPN = '"+replaces +"'";

If the * is at the end it is working fine.

What is my problem is i want to check at the begining too
Since field values contains lot of space at the end , for example "ARC ",I am not able to get the result...

Please help me how to solve this problem...

Thanks & Regards

[Casttro Francis]

HI Priya,

Use Trim in the Query itself. It will work..

EX:

trim(' ARVOD TEC ')

You are using Oracle or DB2 ?? :thumb:

[prriya pratheep]

I am using MYSQL...

I am using select st and display the value(this is for search form)

My code is

if((request.getParameter("Submit")!=null))
{

{
 
String Query = "Select * from inbound_to_future where ";
if(manName.compareTo("NULL")==0)
manName  = "";
manName  =request.getParameter("manName");
 
strmpn=request.getParameter("mpn");
        flag = false;
 
for (int i=0; i < strmpn.length(); i++)
    if (strmpn.charAt(i)=='*')
{
flag = true;
}
 
     String replaces =strmpn.replace('*','%');
if((manName.compareTo("")==0) && (strmpn.compareTo("")!=0))
{
if (flag)
Query = Query +"  MPN like '"+replaces +"'";
else
Query = Query +"  MPN = '"+replaces +"'";
 
}
else
 
if((request.getParameter("manName").compareTo("")==0)&& (strmpn.compareTo("")==0))
{
        out.println("Please select a value");
  Query = Query +"  MPN ='' and ManufacturerName =''";
 

}
if((request.getParameter("manName").compareTo("NULL")==0)&& (strmpn.compareTo("")==0))
{
        out.println("Please select a value");
   Query = Query +"  MPN ='' and ManufacturerName =''";
 

}
else
 
if((manName.compareTo("")!=0)&& (strmpn.compareTo("")==0))
Query = Query +"  ManufacturerName = '"+request.getParameter("manName")+"'";
 
else
if((manName.compareTo("")!=0)&& (strmpn.compareTo("")!=0))
if (flag)
Query = Query +"  MPN like '"+replaces +"'";
else
Query = Query +"  MPN = '"+replaces +"'";
 

Query = Query +" and  (ATS > 0 or SOQ > 0)";
 
out.println(Query);
 

 

 

  out.println(this.getTableHeader("100%"));
                     out.println("<tr class=\"Header\">");
 
out.println("<TD>PartnerName</TD>");
out.println("<TD>ManufacturerName</TD>");
out.println("<TD>MPN</TD>");
out.println("<TD>Region</TD>");
out.println("<TD>ATS</TD>");
out.println("<TD>SOQ</TD>");

out.println("</TR>");
try
{
rs = st2.executeQuery(Query);
}catch(Exception e)
{     
                  con.close();
out.println("Exception in connection : " + e.toString());
}
 

 

while(rs.next())
{
count++;
out.println("<TR class=\""+(count%2==1?"data1":"data2")+"\">");
//out.println("<TD>"+rs.getString("PLANT")+"</TD>");
for(int i=1;i<=6;i++)
{
out.println("<TD>"+rs.getString(i)+"</TD>");

}
out.println("</TR>");
}

out.println("</TABLE></TD></TR></TABLE>");
 

 
}
}

Thanks for the reply
Thanks and Regards