JTDS Issues with Queries “Solved”

I was developing a project and connected my android application successfully with SQL server database. I displayed the data in a list view where all the companies were listed, some of them were containing ‘Pvt. Ltd.’ in names or open brackets () and much more like dash ‘-‘ and other symbols other than the small case and uppercase letters.

But the query execution was not successful when I pass a string containing all these symbols in the query while selecting data for the specific company. So I tried running the query in SQL server and it runs successfully. So the problem I found was these symbols were passed to query but there was some problem so when I replaced all the symbols with ‘ ‘ (empty string) the query gave exact results. So this is what you have to do if you are facing the same issue while getting results due to special characters in the string.

Remove all characters from a string other than numbers and small case and upper case letters. Using pattern matcher and replace all function.

*Declare variables globally

pattern = Pattern.compile("[^a-zA-Z0-9]");
matcher = pattern.matcher(yourstring);
String newstringname= matcher.replaceAll("");

write this in your query string:
SELECT * FROM yourtable where (REPLACE(REPLACE(REPLACE(REPLACE(Replace(REPLACE(Comp_Name,' ',''),'.',''),'+',''),'(',''),')',''),',','') =
REPLACE(REPLACE(REPLACE(REPLACE(Replace(REPLACE('"+newstringname+"'' ',''),'.',''),'+',''),'(',''),')',''),',',''));

Tips:

  1. You can add more replace functions in your query if your string contains more special characters.
  2. Don’t forget to replace special characters from the original string you are passing to query.

[amazon_link asins=’B077PWJP92,B07DRGP8TP,B077PWJLR2,B07BSH6TX3,B0784D8628,B07DC8GFCD|B07B6CMJ7K,B07CGQ8RDQ,B079SQF4BD,B075GWYH3H,B07BHGVB91,B079TMXQ7J,B079VS947C,B07FZTXGLN|B078Y29WZL,B07C62RWD6,B07BHYGLG4,B07C3T8HNL,B07CG435BC|B07B6CMJ7K,B0721QSX3B,B07B6C32JX,B07B6DMZN6,B079VS947C’ template=’ProductCarousel’ store=’lifenews525-21|lifenews-21|lifenews06-21|lifenews0b-21′ marketplace=’IN|UK|FR|IT’ link_id=’ 151ce766-de91-11e8-b9f7-5549db960b72′]

Advertisements

Your Views And Question are Welcome Here!