Solved

SQL Statement using MID Function

Posted on 2009-05-18
5
1,346 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 142

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

914 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now