|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Q: Whitespaces in WHEREHi
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 Show quote "Mohammad Samara" <msam***@gmail.com> wrote in message news:1140799037.330790.244750@t39g2000cwt.googlegroups.com... > string nospacestring= "aaa 65".Replace(" ",""); > > "SELECT [CompanyCode] FROM MyDatabase WHERE [CompanyCode] =" + > nospacestring; > 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 <sendtobi***@gmail.com> wrote in message Show quote news:1140781205.566029.18080@u72g2000cwu.googlegroups.com... >I think you can try out the following > > SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ', > '') = "aaa65". > > Biren > Geoff,
It works in my version of Access, 2003. Kerry Moorman Show quote "G .Net" wrote: > 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 > > > <sendtobi***@gmail.com> wrote in message > news:1140781205.566029.18080@u72g2000cwu.googlegroups.com... > >I think you can try out the following > > > > SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ', > > '') = "aaa65". > > > > Biren > > > > > 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 Show quote "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message news:6112BE51-F7F2-4DF8-9206-5EFD85D16009@microsoft.com... > Geoff, > > It works in my version of Access, 2003. > > Kerry Moorman > > > "G .Net" wrote: > >> 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 >> >> >> <sendtobi***@gmail.com> wrote in message >> news:1140781205.566029.18080@u72g2000cwu.googlegroups.com... >> >I think you can try out the following >> > >> > SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ', >> > '') = "aaa65". >> > >> > Biren >> > >> >> >> 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 Show quote "G .Net" wrote: > 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" <KerryMoor***@discussions.microsoft.com> wrote in message > news:6112BE51-F7F2-4DF8-9206-5EFD85D16009@microsoft.com... > > Geoff, > > > > It works in my version of Access, 2003. > > > > Kerry Moorman > > > > > > "G .Net" wrote: > > > >> 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 > >> > >> > >> <sendtobi***@gmail.com> wrote in message > >> news:1140781205.566029.18080@u72g2000cwu.googlegroups.com... > >> >I think you can try out the following > >> > > >> > SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ', > >> > '') = "aaa65". > >> > > >> > Biren > >> > > >> > >> > >> > > > 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 Show quote "G .Net" wrote: > 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" <KerryMoor***@discussions.microsoft.com> wrote in message > news:6112BE51-F7F2-4DF8-9206-5EFD85D16009@microsoft.com... > > Geoff, > > > > It works in my version of Access, 2003. > > > > Kerry Moorman > > > > > > "G .Net" wrote: > > > >> 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 > >> > >> > >> <sendtobi***@gmail.com> wrote in message > >> news:1140781205.566029.18080@u72g2000cwu.googlegroups.com... > >> >I think you can try out the following > >> > > >> > SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ', > >> > '') = "aaa65". > >> > > >> > Biren > >> > > >> > >> > >> > > > Strange isn't it!
Geoff Show quote "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message news:24AE6ABE-0F97-4A31-B53C-7CA6ABA1B017@microsoft.com... > 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" wrote: > >> 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" <KerryMoor***@discussions.microsoft.com> wrote in message >> news:6112BE51-F7F2-4DF8-9206-5EFD85D16009@microsoft.com... >> > Geoff, >> > >> > It works in my version of Access, 2003. >> > >> > Kerry Moorman >> > >> > >> > "G .Net" wrote: >> > >> >> 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 >> >> >> >> >> >> <sendtobi***@gmail.com> wrote in message >> >> news:1140781205.566029.18080@u72g2000cwu.googlegroups.com... >> >> >I think you can try out the following >> >> > >> >> > SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' >> >> > ', >> >> > '') = "aaa65". >> >> > >> >> > Biren >> >> > >> >> >> >> >> >> >> >> >> 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 Show quote "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message news:24AE6ABE-0F97-4A31-B53C-7CA6ABA1B017@microsoft.com... > 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 > > 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 Show quote "Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message news:%23p5KMncOGHA.3164@TK2MSFTNGP11.phx.gbl... > 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 > > "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message > news:24AE6ABE-0F97-4A31-B53C-7CA6ABA1B017@microsoft.com... >> 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 >> >> > 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' Show quote "G .Net" <nodamnspam@email.com> wrote in message news:uRFWQ7eOGHA.3984@TK2MSFTNGP14.phx.gbl... > 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" <NOSPAMJ3033@Hotmail.com> wrote in message > news:%23p5KMncOGHA.3164@TK2MSFTNGP11.phx.gbl... >> 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 >> >> "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in message >> news:24AE6ABE-0F97-4A31-B53C-7CA6ABA1B017@microsoft.com... >>> 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 >>> >>> >> > > That's a brilliant solution Jim. Thanks!!!
Geoff Show quote "Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message news:eUEYpomOGHA.3196@TK2MSFTNGP09.phx.gbl... > 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" <nodamnspam@email.com> wrote in message > news:uRFWQ7eOGHA.3984@TK2MSFTNGP14.phx.gbl... >> 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" <NOSPAMJ3033@Hotmail.com> wrote in message >> news:%23p5KMncOGHA.3164@TK2MSFTNGP11.phx.gbl... >>> 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 >>> >>> "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> wrote in >>> message news:24AE6ABE-0F97-4A31-B53C-7CA6ABA1B017@microsoft.com... >>>> 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 >>>> >>>> >>> >> >> > > Hi,
Access allows use of REPLACE. Mahesh B. -- Show quote"G .Net" wrote: > 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 > > > <sendtobi***@gmail.com> wrote in message > news:1140781205.566029.18080@u72g2000cwu.googlegroups.com... > >I think you can try out the following > > > > SELECT [CompanyCode] FROM MyDatabase WHERE replace([CompanyCode], ' ', > > '') = "aaa65". > > > > Biren > > > > >  |
|||||||||||||||||||||||
Other interesting topics