Q: Whitespaces in WHERE

  • Thread starter Thread starter G .Net
  • Start date Start date
G

G .Net

Hi

I'm hoping that somebody can help me with the following:

Is there a way to perform a WHERE search where white spaces are ignored? For
example, suppose I have a field called [CompanyCode] which has entries likes
"aaa 65", "bb 5 44" etc.
How can I do something like

SELECT [CompanyCode] FROM MyDatabase WHERE [CompanyCode] = "aaa65"

and I would get at least the first item above?

Thanks in advance

Geoff
 
I think you can try out the following

SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ',
'') = "aaa65".

Biren
 
string nospacestring= "aaa 65".Replace(" ","");

"SELECT [CompanyCode] FROM MyDatabase WHERE [CompanyCode] =" +
nospacestring;
 
Hi Mohammad

I'm afraid you've got the wrong end of the stick. Your solution won't work
because it is
"aaa 65" which exists in the database, not "aaa65".

Geoff
 
Hi Biren

Yes, an interesting way of doing it and it worked for SQL. Unfortunately, I
needed to do it for an Access database and I don't think Access allows the
use of REPLACE. Any other ideas?

Geoff
 
Geoff,

It works in my version of Access, 2003.

Kerry Moorman


G .Net said:
Hi Biren

Yes, an interesting way of doing it and it worked for SQL. Unfortunately, I
needed to do it for an Access database and I don't think Access allows the
use of REPLACE. Any other ideas?

Geoff


I think you can try out the following

SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ',
'') = "aaa65".

Biren
Click to expand...
Click to expand...
 
Hi,

Access allows use of REPLACE.

Mahesh B.
--



G .Net said:
Hi Biren

Yes, an interesting way of doing it and it worked for SQL. Unfortunately, I
needed to do it for an Access database and I don't think Access allows the
use of REPLACE. Any other ideas?

Geoff


I think you can try out the following

SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ',
'') = "aaa65".

Biren
Click to expand...
Click to expand...
 
Puzzling, this is the code I'm using

Try

myConnection.Open()

Dim sql As String = "SELECT * FROM [MyTable] WHERE "

sql &= "REPLACE ([MyDataColumn], ' ','') = 'aaa65'"

Dim oleCmd As OleDbCommand = New OleDbCommand(sql, m_cnMain)

Dim dr As OleDbDataReader= oleCmd.ExecuteReader

If dr.HasRows Then

Do While dr.Read()

Dim name As String = CStr(dr("Name"))

Loop

End If

dr.Close()

myConnection.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try


If I use this I get an exception saying that the REPLACE keyword is not
known

Can you help?

Geoff

Kerry Moorman said:
Geoff,

It works in my version of Access, 2003.

Kerry Moorman


G .Net said:
Hi Biren

Yes, an interesting way of doing it and it worked for SQL. Unfortunately,
I
needed to do it for an Access database and I don't think Access allows
the
use of REPLACE. Any other ideas?

Geoff


I think you can try out the following

SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ',
'') = "aaa65".

Biren
Click to expand...
Click to expand...
Click to expand...
 
Geoff,

I typed the sql with replace directly into an Access 2003 query and it
worked fine. Try using it with double-quote characters as the delimiters:

sql &= "REPLACE ([MyDataColumn], " ","") = 'aaa65'"

Kerry Moorman

G .Net said:
Puzzling, this is the code I'm using

Try

myConnection.Open()

Dim sql As String = "SELECT * FROM [MyTable] WHERE "

sql &= "REPLACE ([MyDataColumn], ' ','') = 'aaa65'"

Dim oleCmd As OleDbCommand = New OleDbCommand(sql, m_cnMain)

Dim dr As OleDbDataReader= oleCmd.ExecuteReader

If dr.HasRows Then

Do While dr.Read()

Dim name As String = CStr(dr("Name"))

Loop

End If

dr.Close()

myConnection.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try


If I use this I get an exception saying that the REPLACE keyword is not
known

Can you help?

Geoff

Kerry Moorman said:
Geoff,

It works in my version of Access, 2003.

Kerry Moorman


G .Net said:
Hi Biren

Yes, an interesting way of doing it and it worked for SQL. Unfortunately,
I
needed to do it for an Access database and I don't think Access allows
the
use of REPLACE. Any other ideas?

Geoff


I think you can try out the following

SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ',
'') = "aaa65".

Biren
Click to expand...
Click to expand...
Click to expand...
 
Geoff,

Here is something I find extremely strange:

I can create a query in Access 2003 that uses the Replace function and it
works just fine from Access.

I cannot use the Replace function in an SQL statement that I execute using
ADO.Net in a VB program.

I cannot even call an Access query (stored procedure) that contains the
Replace function from ADO.Net.

I always get an "undefined function Replace ..." error when trying to use
Replace from ADO.Net.

I don't have a clue why this is the case, but I would love to find out.

Kerry Moorman



G .Net said:
Puzzling, this is the code I'm using

Try

myConnection.Open()

Dim sql As String = "SELECT * FROM [MyTable] WHERE "

sql &= "REPLACE ([MyDataColumn], ' ','') = 'aaa65'"

Dim oleCmd As OleDbCommand = New OleDbCommand(sql, m_cnMain)

Dim dr As OleDbDataReader= oleCmd.ExecuteReader

If dr.HasRows Then

Do While dr.Read()

Dim name As String = CStr(dr("Name"))

Loop

End If

dr.Close()

myConnection.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try


If I use this I get an exception saying that the REPLACE keyword is not
known

Can you help?

Geoff

Kerry Moorman said:
Geoff,

It works in my version of Access, 2003.

Kerry Moorman


G .Net said:
Hi Biren

Yes, an interesting way of doing it and it worked for SQL. Unfortunately,
I
needed to do it for an Access database and I don't think Access allows
the
use of REPLACE. Any other ideas?

Geoff


I think you can try out the following

SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ',
'') = "aaa65".

Biren
Click to expand...
Click to expand...
Click to expand...
 
Strange isn't it!

Geoff

Kerry Moorman said:
Geoff,

Here is something I find extremely strange:

I can create a query in Access 2003 that uses the Replace function and it
works just fine from Access.

I cannot use the Replace function in an SQL statement that I execute using
ADO.Net in a VB program.

I cannot even call an Access query (stored procedure) that contains the
Replace function from ADO.Net.

I always get an "undefined function Replace ..." error when trying to use
Replace from ADO.Net.

I don't have a clue why this is the case, but I would love to find out.

Kerry Moorman



G .Net said:
Puzzling, this is the code I'm using

Try

myConnection.Open()

Dim sql As String = "SELECT * FROM [MyTable] WHERE "

sql &= "REPLACE ([MyDataColumn], ' ','') = 'aaa65'"

Dim oleCmd As OleDbCommand = New OleDbCommand(sql, m_cnMain)

Dim dr As OleDbDataReader= oleCmd.ExecuteReader

If dr.HasRows Then

Do While dr.Read()

Dim name As String = CStr(dr("Name"))

Loop

End If

dr.Close()

myConnection.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try


If I use this I get an exception saying that the REPLACE keyword is not
known

Can you help?

Geoff

Kerry Moorman said:
Geoff,

It works in my version of Access, 2003.

Kerry Moorman


:

Hi Biren

Yes, an interesting way of doing it and it worked for SQL.
Unfortunately,
I
needed to do it for an Access database and I don't think Access allows
the
use of REPLACE. Any other ideas?

Geoff


I think you can try out the following

SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], '
',
'') = "aaa65".

Biren
Click to expand...
Click to expand...
Click to expand...
 
It is because Replace is a Visual Basic for Applications string function and
not an ODBC scalar function.

Access = a front end shell + Visual Basic for Applications with Access
Extensions + Jet database technology

VB.Net = Visual Basic .net WITHOUT the Access exensions + Jet database
technology.

Jet database technology has a number of ODBC Scalar Functions of which the
following String Functions are supported. (according to the online help):

ASCII LENGTH RTRIM
CHAR LOCATE SPACE
CONCAT LTRIM SUBSTRING
LCASE RIGHT UCASE
LEFT
 
Hi Jim

Ah, ok. In that case, is there a way, using the scalar functions that are
accessible, that I can do REPLACE without actually using that keyword?

Geoff
 
Perhaps a wildcard search with the LIKE operator will meet your needs

' this uses the VB.Net string replace function BEFORE it is sent to the
database
dim criteria as string = "aaa 65".Replace(" ","%")
' criteria will then be "aaa%65" and will match zero or more characters
between aaa and 65

dim sql as string = "SELECT [CompanyCode] FROM MyDatabase WHERE
[CompanyCode] LIKE " & criteria

I do NOT recommend that you use string concatenation for your SQL and
instead use parameter queries. Search Google for "sql injection attacks'
 
That's a brilliant solution Jim. Thanks!!!

Geoff

Jim Hughes said:
Perhaps a wildcard search with the LIKE operator will meet your needs

' this uses the VB.Net string replace function BEFORE it is sent to the
database
dim criteria as string = "aaa 65".Replace(" ","%")
' criteria will then be "aaa%65" and will match zero or more characters
between aaa and 65

dim sql as string = "SELECT [CompanyCode] FROM MyDatabase WHERE
[CompanyCode] LIKE " & criteria

I do NOT recommend that you use string concatenation for your SQL and
instead use parameter queries. Search Google for "sql injection attacks'


G .Net said:
Hi Jim

Ah, ok. In that case, is there a way, using the scalar functions that are
accessible, that I can do REPLACE without actually using that keyword?

Geoff
Click to expand...
Click to expand...
 
Back
Top