Post reply

Query-Select the first 5 characters in a field?

  • 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

  • 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

  • January 13, 2010 at 2:17 pm

    #1103322

    Look into the substring function.

    I hope that this helps.

    Thanks...Chris

  • 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]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply