SQL SELECT statement to trim spaces

[FesterSXS]

I have a search feature on one of our sites that includes a standard text search. The search criteria are submitted and an SELECT statement is built to query the Access and/or SQL Server database.

The problem I have is that some of the database content contains spaces that shouldnt really be there. For example, one of our documents is called SC20 Sample Cooler and another similar document is called SC 20 Sample Cooler - additional notes. The space between SC and 20 on the 2nd document shouldnt be there so I would like the search facility to be able to remove the spaces from this field in the DB before it checks against the search criteria.

I have tried using the Replace() function as follows...

strSearchSQL="SELECT * FROM tblName WHERE Replace(title,' ','') LIKE '%" & strSearchCriteria & "%';"

but get the following error...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Replace' in expression.

The database is populated with thousands and thousands of documents, some of which are incorrectly named like this. Eventually we will get around to going through the DB one by one and fixing all the document names, but until then we would like to implement a 'work-around' using the Search facility.

Many thanks Tony

http://www.SpiraxSarco.com/cad

 

[onpnt]

what exactely are you trying to reference the replace function to?
the strSearchCriteria value?

you'll need to either get tthe replace function out of the string and concat it into the string instead of making it part of it or perfomr this function prior to the buiding of the string.

as is the statement is going to pass to the execusion of the sql and it is going to be referenced as part of the sql and not a vbscript function to be performed.

the spaces are spaces entered by the user correct? _________________________________________________________
for the best results to your questions: FAQ333-2924
_{01001111 01101110 01110000 01101110 01110100}

http://www.onpntwebdesigns.com

[/sub]

 

[FesterSXS]

Using my example above, I would like someone to be able to enter SC20 and get both documents mentioned above to be returned. The spaces would need to be stripped from the db content before the LIKE comparison is applied to it.

hope that makes sense

For clarification, in my SQL string, title is the name of my db field. Tony

http://www.SpiraxSarco.com/cad

 

[JamesLean]

Tony,
Your query should work fine in SQL Server. Only thing to note is that by using the Replace function, it won't be able to utilise any indexes you may have on the title column.

Unfortunately, the Replace function does not exist in Access which is why you're getting the error. You could try posting in the Access forum to see if anyone has any clever workarounds? --James

 

[onpnt]

wow, where am I this morning.

I get it now.

one thing you could do for access is the ltrim which is available. if the beginning portion prior to the numeric value is unique to the naming of the files. _________________________________________________________
for the best results to your questions: FAQ333-2924
_{01001111 01101110 01110000 01101110 01110100}

http://www.onpntwebdesigns.com

[/sub]

 

[BDC2]

Also, the like modifier for Access is * not % BDC.

 

[FesterSXS]

onpnt, I'm afraid the document naming procedure in this company is rarely adhered to. The document titles have no real consistency across our range of products, let alone similar ones like the ones above.

Fortunately the majority of our documents are hosted within a SQL Server DB but there are a still a vast number that are not.

JamesLean, thanks for the clarification about Access not supporting Replace(). I shall ferret around some Access gurus to see what they can rustle up

Thanks again guys Tony

http://www.SpiraxSarco.com/cad

 

[JamesLean]

Check out this link - you may be able to tweak this to get something you could use:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210433

--James

 

[FesterSXS]

thanks - i'll have a look Tony

http://www.SpiraxSarco.com/cad