• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 982
  • 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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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