?
Solved

SQL Statement using MID Function

Posted on 2009-05-18
5
Medium Priority
?
1,354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 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 400 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 200 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 200 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

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 ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

770 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