?
Solved

Access - SQL _ Query syntax

Posted on 2011-10-17
42
Medium Priority
?
335 Views
Last Modified: 2012-08-13
I need this query to work in SQL and I am getting a syntax error - it doesn't like "instr" - does anyone know the correct syntax?

Thanks,
John

SELECT     EMAIL.Email, RIADatabaseFirm.Websites, mid(EMAIL.Email, instr(EMAIL.Email, [@]) + 1) AS Expr1
FROM         EMAIL CROSS JOIN
                      RIADatabaseFirm
WHERE     (RIADatabaseFirm.Websites LIKE [*] & mid(EMAIL.Email, instr(EMAIL.Email, [@]) + 1))

Open in new window

0
Comment
Question by:JohnMac328
  • 13
  • 10
  • 7
  • +4
42 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36980555
instr is not a valid SQL Server command and neither is mid.

What are you trying to do?
0
 

Author Comment

by:JohnMac328
ID: 36980573
Compare the email field where for example @aol.com will match up to www.aol.com in the other table which is the Websites column
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 36980581
sql server doesn't use instr.

Use CHARINDEX instead.

YOu just have to reverse the order. So instead of:

instr(EMAIL.Email, [@]) + 1))

use

CHARINDEX ( [@]) , EMAIL.Email + 1))

Also change Mid to substring
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 18

Expert Comment

by:Cluskitt
ID: 36980584
The MS SQL equivalents to those are CHARINDEX for InStr and SUBSTRING for Mid. They work basically the exact same way, just have a different name.
0
 

Author Comment

by:JohnMac328
ID: 36980599
Here is what I have now and it gives me a error near ')'
SELECT     EMAIL.Email, RIADatabaseFirm.Websites, SUBSTRING(EMAIL.Email, CHARINDEX(EMAIL.Email, [@]) + 1,) AS Expr1
FROM         EMAIL CROSS JOIN
                      RIADatabaseFirm
WHERE     (RIADatabaseFirm.Websites LIKE [*] & SUBSTRING(EMAIL.Email, CHARINDEX(EMAIL.Email, [@]) + 1,))

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 36980600
You have posted the Q to both an Access zone and a SQL Server zone.

The answer to your Q is different in each case. You need to clarify which product you are using to run the SQL.
0
 
LVL 36

Expert Comment

by:Norie
ID: 36980611
Try CHARINDEX for InStr and SUBSTRING in place of MID.

SUBSTRING(EMAIL.Email, CHARINDEX([@], EMAIL.Email)+1, LEN(EMAIL.Email))

By the way why are @ and * in square brackets?

I've only seen that syntax used with field/table names.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 36980616
CHARINDEX(EMAIL.Email, [@]) + 1,)
This is wrong. You forgot something here or added an extra ,
0
 

Author Comment

by:JohnMac328
ID: 36980623
This is from an Access database that locks up when I try to run this query so I am trying to get it to work on SQL 2008 - the @ is part of the email address
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 36980629
you didn't use my recommendation.

Here:

SELECT     EMAIL.Email, RIADatabaseFirm.Websites, SUBSTRING(EMAIL.Email, CHARINDEX([@]) + 1,EMAIL.Email) AS Expr1
FROM         EMAIL CROSS JOIN
                      RIADatabaseFirm
WHERE     (RIADatabaseFirm.Websites LIKE [*] & SUBSTRING(EMAIL.Email, CHARINDEX( [@]) + 1, EMAIL.Email))

Open in new window

0
 

Author Comment

by:JohnMac328
ID: 36980646
sammySeltzer - yes I did - I copied your latest and it still gives me the incorrect syntax near ')'
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 36980651
Check parenthesis (..) to make sure they are equal.

Also, not sure if it should be @ +1, email

or @, email+1.

It all depends on what you are trying to do.


0
 

Author Comment

by:JohnMac328
ID: 36980662
I want to compare the email field where for example @aol.com will match up to www.aol.com in the other table which has the Websites column
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 36980731
WHERE CHARINDEX(RIADatabaseFirm.Websites, RIGHT(EMAIL.Email, LEN(EMAIL.Email)-CHARINDEX(EMAIL.Email,'@')))>0
0
 
LVL 36

Expert Comment

by:Norie
ID: 36980732
Why is @ in square brackets?

Shouldn't it be in quotes?

SUBSTRING(EMAIL.Email, CHARINDEX('@', EMAIL.Email)+1, LEN(EMAIL.Email))

CHARINDEX wil return the position of the first occurence of '@' in the string.

Then SUBSTRING will return the rest of the string starting after the position of the '@'.
0
 

Author Comment

by:JohnMac328
ID: 36980744
Cluskitt - I still get incorrect syntax near ')'
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 36980759
I tried it on my end and it runs fine. Make sure it replaces the whole WHERE clause.
0
 

Author Comment

by:JohnMac328
ID: 36980770
Here is my whole query
SELECT     EMAIL.Email, RIADatabaseFirm.Websites, SUBSTRING(EMAIL.Email, CHARINDEX([@],) + 1, EMAIL.Email) AS Expr1
FROM         EMAIL CROSS JOIN
                      RIADatabaseFirm
WHERE     (CHARINDEX(RIADatabaseFirm.Websites, RIGHT(EMAIL.Email, LEN(EMAIL.Email) - CHARINDEX(EMAIL.Email, '@'))) > 0)

Open in new window

0
 

Author Comment

by:JohnMac328
ID: 36981634
Here is the screen shot of the error screen
0
 
LVL 36

Expert Comment

by:Norie
ID: 36981942
Does the rest of that mesage mention EMAIL.email on it's own?
0
 

Author Comment

by:JohnMac328
ID: 36981969
What I posted it what displayed on the screen - not sure what you are asking
0
 
LVL 36

Expert Comment

by:Norie
ID: 36982010
Sorry, the full image didn't get loaded - been having some similar problems elsewhere.

Anyway, have you tested the suggested SQL in SQL Server?

Are you actually using [@] in the code or are you using '@'?
0
 

Author Comment

by:JohnMac328
ID: 36982026
Here it is copied from SQL
SELECT     EMAIL.Email, RIADatabaseFirm.Websites, SUBSTRING(EMAIL.Email, CHARINDEX([@],) + 1, EMAIL.Email) AS Expr1
FROM         EMAIL CROSS JOIN
                      RIADatabaseFirm
WHERE     (CHARINDEX(RIADatabaseFirm.Websites, RIGHT(EMAIL.Email, LEN(EMAIL.Email) - CHARINDEX(EMAIL.Email, '@'))) > 0)

Open in new window

0
 
LVL 36

Expert Comment

by:Norie
ID: 36982134
That gives the error when you execute it in SQL Server Management Studio?

Is there anything else involved here, a view perhaps?

The reason I ask is because the error message refers to Net SqlClient Data Provider.

I'm still baffled by the '@' being converted to [@].

I suppose you could try replacing '@' with CHAR(64), 64 is the ASCII code for @.
0
 

Author Comment

by:JohnMac328
ID: 36982167
Did not seem to make a difference
0
 
LVL 36

Expert Comment

by:Norie
ID: 36982204
What didn't seem to make a difference?

Is there definitely nothing else going on?

Is the data from a lined source?

Do other queries run without problems?

0
 

Author Comment

by:JohnMac328
ID: 36982223
Same ')' syntax error
0
 
LVL 36

Expert Comment

by:Norie
ID: 36982697
John

Can you please answer the other questions?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36983903
>>Same ')' syntax error <<
Sure, your query is  wrong.  You need to read up on the syntax for CHARINDEX and how string literals are represented using T-SQL.
0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 2000 total points
ID: 36984493
Your problem lies not in the WHERE, but in the SELECT:
SELECT     EMAIL.Email, RIADatabaseFirm.Websites, RIGHT(EMAIL.Email, LEN(EMAIL.Email) - CHARINDEX(EMAIL.Email, '@')) AS Expr1
FROM         EMAIL CROSS JOIN
                      RIADatabaseFirm
WHERE     (CHARINDEX(RIADatabaseFirm.Websites, RIGHT(EMAIL.Email, LEN(EMAIL.Email) - CHARINDEX(EMAIL.Email, '@'))) > 0)

Open in new window


Your charindex function had an extra ,
You can simply use a right function though.
0
 

Author Closing Comment

by:JohnMac328
ID: 36985501
Thanks Cluskitt!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36985532
>>Your problem lies not in the WHERE, but in the SELECT:<<
Actually the SELECT also has CHARINDEX used wrong, too.  Clearly someone has mislead the author as to how CHARINDEX works.  It is NOT the same syntax as InStr as someone suggested.  Not even close.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 36985564
I've always used CHARINDEX in its shortest form, and it's the same as InStr. (StringToBeSearched,StringToFind) As long as you replace "" with '', but that's because of the way SQL handles strings as opposed to VB.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36985615
>>I've always used CHARINDEX in its shortest form, and it's the same as InStr.<<
Than I suggest you revist SQL Server's BOL. This is what it states:

CHARINDEX ( expression1 ,expression2 [ , start_location ] )
expression1
Is a character expression that contains the sequence to be found. expression1 is limited to 8000 characters.

expression2
Is a character expression to be searched.

start_location
Is an integer or bigint expression at which the search starts. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expression2.


Here is the VBA documentation for InStr (yes I do realize that JET<> VBA):
InStr([start, ]string1, string2[, compare])
start
Optional. Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. If start contains Null, an error occurs. The start argument is required if compare is specified.
string1
Required. String expression being searched.
string2
Required. String expression searched for.
compare
Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values. If omitted, a binary comparison is performed.


Can you see the difference?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36985622
For example in the author's question, if they are looking for an "@" in Email than this
CHARINDEX(EMAIL.Email, '@')

Should be written:
CHARINDEX('@', EMAIL.Email)
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 36985623
Yes, they're switched. I always confuse which is first anyway. Most of the time I end up switching the arguments after the code is prepared, both in SQL and VB :P
Anyway, you are correct, they're not the same. And my example should be switched.
0
 
LVL 36

Expert Comment

by:Norie
ID: 36985671
So can I get this right?

This:

CHARINDEX('@', EMAIL.Email)

is the correct expression for finding @.

But the solution works with CHARINDEX(EMAIL.Email, '@")?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36985695
>>But the solution works with CHARINDEX(EMAIL.Email, '@")? <<
Relax.  It is not the end of the world, you cannot eat poinks.
0
 
LVL 36

Expert Comment

by:Norie
ID: 36985789
Poinks?

I was really just wondering if I'd got it wrong, because I do the same thing with InStr - always get it the wrong way round and have to fix it.:)

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36987623
>>Poinks?<<
A reference to the point system used by EE.

>>I was really just wondering if I'd got it wrong<<
The author just did not bother to test the solution provided, before awarding points.  It happens all the time.
0
 
LVL 36

Expert Comment

by:Norie
ID: 36988353
I thought you might have meant 'oinks' as in pigs.:)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36989937
>>I thought you might have meant 'oinks' as in pigs.:) <<
No.  See here:
http://www.experts-exchange.com/Other/Lounge/Q_22129399.html
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question