Query-Select the first 5 characters in a field?

• shopping-1141731

  SSC Veteran

  Points: 260

  More actions

  January 12, 2010 at 8:38 am

  #215687

  Hi All,

  Still very new to query writing so I'm hoping this is a simple one. We're a book publisher and I'm trying to pull back order numbers from our BO table and need to get the customer "Bill - To" name and number.

  The problem is that our BO table contains our customers "Ship-To" numbers (a 10 digit number) and to get the customer name I need to link to our Customer table which has our customers "Bill - To" numbers (a 5 digit number). Our customers typically have multiple ship-to destinations.

  Note that the first 5 digits of the "Ship-To" number IS the "Bill-To" number.

  How in my SQL code can I select only the first "5" digits of the string in the customer fields and tie them to the 5 digit field in our customer table?

  For what it's worth this is the code our developer gave us (currently unreachable) but it's pulling the customer ship to. Thanks!

  select tm.Title,tm.primary_id ISBN13,

  cust_D,c.Name,Doc_Ref INVOICE_ID,

  isnull( sum(BO.DEL_QTY),0) BO_DEL_QTY,

  isnull(sum(BO.UNS_QTY),0) BO_UNS_QTY,

  isnull(sum(BO.DUE_QTY),0) BO_DUE_QTY

  from

  dbo.TM_VVPII_XSL tm

  inner JOIN DBO.BACK_ORDERS BO ON Tm.SBN=BO.SBN

  inner join Custmas c on c.cust=bo.cust_d

  where

  ANSWER_CODE IN ('NYP','OOS')

  and bo.Brick in (select Brick from dbo.Brick_Country_Buckets)

  group by tm.Title,tm.primary_id,cust_D,Doc_Ref,c.name

  order by tm.Title

• WayneS

  SSC Guru

  Points: 95461

  More actions

  January 12, 2010 at 8:45 am

  #1102485

  JOIN to the CUSTOMER table ON bo.cust_d LIKE CUSTOMER.cust + '%'

  This will match to the customer table where the cust_d starts with the customer cust field.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• CGSJohnson

  SSCertifiable

  Points: 7208

  More actions

  January 13, 2010 at 2:17 pm

  #1103322

  Look into the substring function.

  I hope that this helps.

  Thanks...Chris

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  January 13, 2010 at 2:23 pm

  #1103328

  This is probably a quick and dirty fix until your developer is available, but you could try the LEFT function as follows:

  select tm.Title, tm.primary_id ISBN13,

  cust_D, c.Name, Doc_Ref INVOICE_ID,

  isnull( sum(BO.DEL_QTY),0) BO_DEL_QTY,

  isnull(sum(BO.UNS_QTY),0) BO_UNS_QTY,

  isnull(sum(BO.DUE_QTY),0) BO_DUE_QTY

  from dbo.TM_VVPII_XSL tm

  inner JOIN DBO.BACK_ORDERS BO ON Tm.SBN=BO.SBN

  inner join Custmas c on LEFT(c.cust, 5) = LEFT(bo.cust_d, 5)

  where ANSWER_CODE IN ('NYP','OOS')

  and bo.Brick in (select Brick from dbo.Brick_Country_Buckets)

  group by tm.Title, tm.primary_id, cust_D, Doc_Ref, c.name

  order by tm.Title

  Cheers

  ChrisM@home

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]