• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1357
  • Last Modified:

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

0
Steven O'Neill
Asked:
Steven O'Neill
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SQL server does not have MID() function, you need to use substring() function!
0
 
Raja Jegan RSQL 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
 
RiteshShahCommented:
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
 
bljakCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now