Solved

SQL Statement using MID Function

Posted on 2009-05-18
5
1,348 Views
Last Modified: 2012-05-07
I'm currently writing a SQL statement which I have a slight problem with. The code involved is below but I'm looking to only use the first 10 characters of the dbo.FilteredContact.new_clientreflegacy field. So in order to do this I have the following as my last line of the query:

ANCHORSERV.S3CUSTDB.dbo.CustomerTable AS CustomerTable_1 ON mid(dbo.FilteredContact.new_clientreflegacy,1,10) = CustomerTable_1.CustomerNumber

However SQL keeps throwing an error about the MID function and I'm wondering what I'd doing wrong here?

Any suggestions greatly appreciated.
SELECT     dbo.FilteredContact.contactid, dbo.FilteredContact.new_clientreflegacy, PS_Integration.dbo.PS_Contact_Base.ContactGUID
FROM         dbo.FilteredContact INNER JOIN
                      PS_Integration.dbo.PS_Contact_Base ON dbo.FilteredContact.contactid = PS_Integration.dbo.PS_Contact_Base.ContactGUID INNER JOIN
                      ANCHORSERV.S3CUSTDB.dbo.CustomerTable AS CustomerTable_1 ON dbo.FilteredContact.new_clientreflegacy = CustomerTable_1.CustomerNumber

Open in new window

0
Comment
Question by:Steven O'Neill
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 24410380
SQL server does not have MID() function, you need to use substring() function!
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 100 total points
ID: 24410388
There is no Mid function in SQL Server.
Instead use SUBSTRING as given below.

ANCHORSERV.S3CUSTDB.dbo.CustomerTable AS CustomerTable_1 ON SUBSTRING(dbo.FilteredContact.new_clientreflegacy,1,10) = CustomerTable_1.CustomerNumber
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 50 total points
ID: 24410406
there is no MID in SQL and you don't need it actually, if you need to have first 10 char than why don't you use LEFT(dbo.FilteredContact.new_clientreflegacy,10)?
0
 
LVL 4

Assisted Solution

by:bljak
bljak earned 50 total points
ID: 24410411
If you are interested in ONLY FIRST LEFT 10 chars you can use this.
Or instead as suggested, replace "MID" with "SUBSTRING" in your original query and it should work
SELECT     dbo.FilteredContact.contactid, dbo.FilteredContact.new_clientreflegacy, PS_Integration.dbo.PS_Contact_Base.ContactGUID
FROM         dbo.FilteredContact INNER JOIN
                      PS_Integration.dbo.PS_Contact_Base ON dbo.FilteredContact.contactid = PS_Integration.dbo.PS_Contact_Base.ContactGUID INNER JOIN
                      ANCHORSERV.S3CUSTDB.dbo.CustomerTable AS CustomerTable_1 ON left(dbo.FilteredContact.new_clientreflegacy,10) = CustomerTable_1.CustomerNumber

Open in new window

0
 
LVL 2

Author Closing Comment

by:Steven O'Neill
ID: 31582525
This site never ceases to amaze me...thanx for this guys, all very worthy and I've split the points as they all meet my requirements is I needed.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

856 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