Problems with SQL Command

[Baradaran]

Hi Everyone,

I have a problem which makes me jump out of my socks.

There is a database with 6 fields, containing ID, PRODUCT, DESCRIPTION, FIELD1, FIELD2, FIELD3 with about 4000 records.

_____________________________________________________
| ID | PRODUCT | DESCRIPTION | FIELD1 | FIELD2 | FIELD 3 |
=========================================
| 1 | MP 10 005 | Power Cord | 135 gr. | PW CR | BLACK |
========================================= ... etc.

The product fields contain our own product number, which consist of 2 white spaces:
MP as our Brand name
10 product range
005 product ID

In our product catalogue, customers can search for our PRODUCTS, however sometimes they do not consider the white spaces, so they enter MP10 005 or similar. In my sql I want to remove the spaces from our product, remove the spaces from the search term, and compare.

The problem is, I am not able to use an sql command to remove the spaces from the product item.

I have solved the problem by duplicating the product column and inserting the products into a second column without spaces, which is used when searching.

Is there any sql command which can remove spaces from data extracted from the database?

I thank you for your time.

[QVeen72]

Hi,

If using Access, try using Replace Function:

"Select * From MyTable Where " _
& " Replace(ProductName,' ','') Like '*" _
& Replace(txtName.text," ","") & "*'"

Regards
Veena

[Baradaran]

Dear Veena,
Thank you for your reply. I did try it, but I receive the following error:

Undefined function 'Replace' in expression.

I am using Access 2000, and the connection I am using is Microsoft.Jet.OLEDB.4.0.

[dspnhn]

i need to look at your code......if u don't mind or some NDA is not violated

[dspnhn]

use this.........

Private Sub Command1_Click()
'********i just assume ur connection is in form_load..............this is just for the query
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\db1.mdb;"
'**********************************************************************
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open ("select * from det order by srno"), db, adOpenDynamic, adLockOptimistic
'**********put your table instead of det

If rs.RecordCount > 0 Then
rs.MoveFirst
Do While rs.EOF <> True
'***********************************
MsgBox rs!product
'*******************flag this msgbox also
fstocc = InStr(1, rs!product, " ")
fst = Left(rs!product, InStr(1, rs!product, " "))

sndocc = InStr(fstocc, rs!product, " ")
snd = Mid(rs!product, fstocc, InStr(fstocc, rs!product, " "))

lstocc = InStr(sndocc, rs!product, " ")
lst = Right(rs!product, InStr(sndocc, rs!product, " "))

prod = Trim(fst) & Trim(snd) & Trim(lst)

msgbox prod

'****first run the query only and deactivate the db command if u see ur desired result then '****only activate db command and deactivate above msgbox
'db.BeginTrans
'db.Execute ("update det set product='" & Trim(prod) & "' where product='" & rs!product & "'")
'db.CommitTrans
'********************
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End If

End Sub

[Baradaran]

Dear dspnhn,
Thank you for the code. The problem is, this code gets about 4000 records, removes the spaces between the data, compares it to the search term, if it suits, it accepts it, if not, it goes to the next record. This procedure is taking a lot of time, escpecially, if the user wants to make an extended search on the product description as well.

I am rather looking for a solution to remove the spaces within the sql command. Qveen has already suggested a god solution, which is not working in my code.

"Select * From MyTable Where " _
& " Replace(ProductName,' ','') Like '*" _
& Replace(txtName.text," ","") & "*'"

I believe there is an option to remove white spaces in SQL, however, my googlin' did not deliver any results.

[dspnhn]

try this then...........

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open ("select * from det order by srno"), db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While rs.EOF <> True
db.BeginTrans
db.Execute ("update det set product='" & Replace(rs!product, " ", "") & "' where product='" & rs!product & "'")
db.CommitTrans
rs.MoveNext
Loop
End If

it may take some time to update all records.........i will meanwhile look for other alternatives

[jireh]

A better way since you are using begin & commit transaction...

On Error Goto ErrHandler

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open ("select * from det order by srno"), db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
     rs.MoveFirst

     db.BeginTrans

     Do While Not rs.EOF
            db.Execute ("update det set product='" & Replace(rs!product, " ", "") & "' where product='" & rs!product & "'")
            rs.MoveNext
     Loop

     db.CommitTrans
End If

ErrHandler:
   MsgbBox Err.Description
   db.RollbackTrans