SQL Statement using MID Function

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

LVL 2
Steven O'NeillSolutions ArchitectAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
SQL server does not have MID() function, you need to use substring() function!
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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
 
RiteshShahConnect With a Mentor Commented:
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
 
bljakConnect With a Mentor Commented:
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
 
Steven O'NeillSolutions ArchitectAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.