Hello!

I need to remove a specific character from a feild in sql query. Means I need to use REPLACE function on sql query to match with specific criteria.

Something like this,

FULL_NAME = "sun is resing" (Database field name and it's vlaue)


sql = "select * from customers where REPLACE(FULL_NAME, "s", "") = 'un i reing'

Any idea, Any help will really be appriciated

__________________
Zeeshan Dar
itHighway
Get in Touch with People Texting your Website
Advanced Real Estate Listings Solution - FREE Hosting

I have never used replace in a query (not to say icant be done). May I suggest another method. To achieve this I tend to use conditions, either to execute different queries (1), or to build a query(2).

Eg (1)
If request.form("currentStatus") = "True" then
sql = "UPDATE serviceCallDocs SET approved=0 WHERE..."
ELSEIF request.form("currentStatus") = "False" then
sql = "UPDATE serviceCallDocs SET approved=1 WHERE..."
END IF

Or (2)
sql = "UPDATE tblName SET "
if [something1] = [something1] then
     sql = sql & " FieldName "
else
     sql = sql & " someOtherFieldName "
end if
sql = sql & " = "
if [something2] = [something2] then
     sql = sql & " someValue "
Else
     sql = sql & " someOthervalue "
End if
sql = sql & " WHERE... "
response.write sql & "==<br>"

Wind is your friend
Matt

Thanks for your reply Matt.
But actually this is a search program with 500+ records and what I need is,

When I search it should do a checking of word ‘s’ or removing the word ‘s’ if one was added
Such as I search for “car”. It also includes “cars”
OR
search for "cars". It also includes "car"

Any ideas..???

I would still build queries based on conditions using the LIKE operator.

;;;;I search for “car”. It also includes “cars”
sql = "SELECT someField FROM tblename WHERE somefield LIKE '%car%'"
Would do it, however it would also pull all other records starting with the letters car as well.

;;;search for "cars". It also includes "car"
Not so strait forward. I would create a table of words and thier aliases. Query this table on the key word (cars) then get all the aliases you have defined that are equal to your key word and search for these.

Wind is your friend
Matt

Hmm, how about this
;;;search for "cars". It also includes "car"
check the last character of keyword and if it contain "s" in last simply remove it. So the condition will become exactly as first one. - Thanks

If that works, great - but would it?

I guess if all plural names became singular after dropping the s it would work. What about a search for pennies, dropping the s wouldnt give us all results for penny.

Wind is your friend
Matt