using trim with SQL

[Guest]

I've attempted to use trim() im my SQL statement but It throws an error saying its not a valid function.

This isnt the statement but this is the context im using it in.

<cfquery datasource="#datasources.DATADSN#">
INSERT INTO dbo.People (Christian)
<cfif IsDefined("FORM.Christian") AND #FORM.Christian# NEQ "">
trim('#FORM.Christian#')
<cfelse>
NULL
</cfif>
</cfquery>

If you cant use it at this point, where can you use it?

[Guest]

It looks like you're using SQL Server. SQL Server 2000 doesn't have a unified trim function, you need to left and right-trim. Try LTrim ( Rtrim ('#FORM.Christian#')).

[drforbin1970 · Accepted solution]

If you are just trying to trim leading/trailing whitespace, here is the correct syntax:

<cfquery datasource="#datasources.DATADSN#">
INSERT INTO dbo.People (Christian)
<cfif IsDefined("FORM.Christian") AND Trim(FORM.Christian) NEQ "">
#Trim(FORM.Christian)#
<cfelse>
NULL
</cfif>
</cfquery>