Spaces in column value

[awhitaker]

I have a column table in which the values have spaces, i.e. "2005 CRA 696." In
my query, I want to account for various ways people may type that number into a
form, such as without any spaces - "2005CRA696" or with only "05CRA696." I
cannot modify the structure of the table. Any suggestions?

[Abinidi]

Its not good practice to have spaces in a column name. It is better to use underscores or not at all .

[paross1]

In a query, you can rename the column with an alias without the spaces. Make
sure that you place the original column name within brackets (depending on your
database). Something like this:

SELECT [2005 CRA 696] AS 2005_CRA_696
FROM yourtable....

Phil

[awhitaker]

Thanks for your help, but I must not have been clear on my problem. The column
name doesn't have a space - the values in the column have spaces, and I need to
match those against user input that may or may not contain the same spaces.

[Dan Bracuk]

Are the spaces always in the same place? If so, use string functions on your form variable to create a new variable in the expected format. Use that format in your query.

[reenaroy]

Try to use this UDF for this purpose,

<cffunction name="getCorrectFormat" output="true" returntype="string"
access="public">
<cfargument name="str" type="any" required="true"/>
<cfset FirstFour=val(mid(str,1,4))>
<cfset FirstFourLen=len(FirstFour)>
<cfif FirstFourLen eq 1>
<cfset FirstFour="200" & FirstFour>
</cfif>
<cfset LastThree=right(str,3)>
<cfset newstr=trim(replacenocase(str,LastThree,"","all")) >
<cfset MiddelThree=right(newstr,3)>

<cfset correctformatStr=FirstFour & " " & MiddelThree & " " & LastThree>
<cfreturn correctformatStr>
</cffunction>


<cfset str="05CRA696">
<cfquery datasource="xyz" name=abc>
select * from tablename where columnname='#getCorrectFormat(str)#'
</cfquery>

[merrillaldrich]

Just strip all whitespace out of the input and the value(s) from the table and compare the results. You can use a replace function or regular expressions.