• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 967
  • Last Modified:

Error 'Trim' is not a recognized function name.

Hello experts.
I'm trying to use the test coldfusion-code on this page:http://www.geodatas.net/integration.asp#cf

<cfquery datasource="database_name" name="seek_own_zip">
      select lat, lng from zips  where trim(zip) = '#trim(form_zip)#'
</cfquery>

but i get an error:'Trim' is not a recognized function name.
the database values are like xxx xx.


Any help?
0
Panos
Asked:
Panos
  • 5
  • 2
  • 2
  • +1
2 Solutions
 
ZvonkoSystems architectCommented:
You did not state what DB engine you are using in that query.
In some DB system is TRIM() not defined. You can try wehther the LTRIM(RTRIM(zip)) would work but why do you trim at query time and not at INSERT time?

Therefore replace simply the trim(zip) with: zip

 
0
 
PanosAuthor Commented:
Hi zvonko
I can't trim at insert time because I have downloaded  the db with all postalcodes as it is.The problem is that not all countries have the same format.(xxx xx......).
That's why the  form textfield field (search form) has no format.(javascript validation)
I 'm using SQL server.
where LTRIM(RTRIM(zip)) = '#LTRIM(RTRIM(form.zip))#' is still not working

0
 
PanosAuthor Commented:
May be i need a function to remove any white space at all.Ltrim is for start and RTIM for end.In my example i have to remove the whitespace inside the text(xxx xx)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
geowrianCommented:
TRIM(), even in the DBMS that do support it, does not touch anything after the first non-whitespace character or after the last non-whitespace character. if you want to remove all whitespace, the best option may be to do:
<cfquery datasource="database_name" name="seek_own_zip">
      select lat, lng from zips  where REPLACE(zip,' ','') = REPLACE('#form_zip#',' ','')
</cfquery>

<!---Obviously, perform validation on form_zip to avoid SQL injection attacks--->

Open in new window

0
 
PanosAuthor Commented:
Yes geowrian.The geowrian is the function i need.
Thank you
0
 
PanosAuthor Commented:
Thank you both.
More points as he did solve my problem.
regards
panos
0
 
_agx_Commented:
@panosms,

If it's your database,  just run a one-time update using geowrian's function. Then you won't have to keep using REPLACE()  in _every_ query.

UPDATE zips  
SET   REPLACE(zip,' ','')
0
 
geowrianCommented:
I agree that using the REPLACE function on each select query is more resource intensive. My understanding is that the original formatting needed to be preserved, so that's why I didn't suggest doing a one-time UPDATE query.
0
 
_agx_Commented:
@geowrian,

Yes, I really wasn't sure either. But I thought I'd throw it out there. Just in case :)
0
 
PanosAuthor Commented:
Thank you for your comments.
I have allready done this because i had to use you the function (replace) twice.Fitst in the query and second when i was trying to compare values with the output list with values that should have the xxxxx format.So changing the format in db column was the best solution.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now