Q: Whitespaces in WHERE

[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

 

[sendtobiren]

I think you can try out the following

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

Biren

 

[Mohammad Samara]

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

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

 

[G .Net]

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

 

[G .Net]

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

 

[Guest]

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

 

[Guest]

Hi,

Access allows use of REPLACE.

Mahesh B.
--

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

 

[G .Net]

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

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

 

[Guest]

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

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

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

 

[Guest]

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

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

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

 

[G .Net]

Strange isn't it!

Geoff

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

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

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

 

[Jim Hughes]

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

 

[G .Net]

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

 

[Jim Hughes]

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]

That's a brilliant solution Jim. Thanks!!!

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'

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