asked on

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?

SOLUTION

Zvonko

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Panos

ASKER

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

Panos

ASKER

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)

ASKER CERTIFIED SOLUTION

geowrian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Panos

ASKER

Yes geowrian.The geowrian is the function i need.
Thank you

Panos

ASKER

Thank you both.
More points as he did solve my problem.
regards
panos

_agx_

@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,' ','')

geowrian

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.

_agx_

@geowrian,

Yes, I really wasn't sure either. But I thought I'd throw it out there. Just in case :)

Panos

ASKER

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.