Query-Select the first 5 characters in a field?

Question

Post reply

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

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

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 · Answer 2

Look into the substring function.

I hope that this helps.

Thanks...Chris

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

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]