May 11, 2015 at 11:10 am
Here is a sample order # we used for one of our shipments: BL-53151-24954-1-0001-33934
I need to extract the "24954" portion of that order # while within an INNER JOIN, but not sure how.
My problem is we have 2 order tables: OrderTable1 contains a field with the full order #. OrderTable2 contains a field with only the "24954" portion. I need to JOIN on these 2 fields somehow.
So an example would be the following:
OrderTable1.Full_Order_No: BL-53151-24954-1-0001-33934
OrderTable2.Order_No: 24954
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM
OrderTable1 ot1
INNER JOIN OrderTable2 ot2 ON ot2.Order_No = [do something here to truncate ot1.Full_Order_No]
How can I do this?
A few notes, if it helps:
-the 1st part of the order number, "BL-53151-" will ALWAYS be the same. It's our client # which will never change for the purpose of this query.
-The portion I need (24954) can be more or less than the 5 current digits.
-There will always be 6 portions to the order number, split up between 5 dashes.
Thanks in advance for any help
May 11, 2015 at 12:22 pm
The best option is to store your full order number in separate columns so you don't have to worry about dividing it later (it's easier to join for presentation than dividing it). This would also improve performance. You could create a computed column to help you build queries easier. If the computed column is persisted, you could even create an index on it.
Meanwhile, I just created some solutions that can work for you. The last one uses the DelimitedSplit8k which you can find in the following article, as well as a detailed explanation on how does it work: http://qa.sqlservercentral.com/articles/Tally+Table/72993/
--Generating sample data (please include this with your posts for faster solutions)
CREATE TABLE #OrderTable1( Full_Order_No varchar(50)) ;
INSERT INTO #OrderTable1 VALUES( 'BL-53151-24954-1-0001-33934');
INSERT INTO #OrderTable1 VALUES( 'BL-53151-249543245-1-0001-33934');
INSERT INTO #OrderTable1 VALUES( 'BL-5311-249-1-0001-33934');
CREATE TABLE #OrderTable2( Order_No varchar(10));
INSERT INTO #OrderTable2 VALUES( '24954');
INSERT INTO #OrderTable2 VALUES( '249543245');
INSERT INTO #OrderTable2 VALUES( '249');
-- Coding for a static length Client number
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM #OrderTable1 ot1
INNER JOIN #OrderTable2 ot2 ON ot2.Order_No = SUBSTRING( ot1.Full_Order_No, 10, CHARINDEX( '-', ot1.Full_Order_No, 10) - 10)
-- Coding for a dynamic length Client number
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM #OrderTable1 ot1
INNER JOIN #OrderTable2 ot2 ON ot2.Order_No = SUBSTRING( ot1.Full_Order_No
, PATINDEX( '%[0-9]-[0-9]%', ot1.Full_Order_No) + 2
, CHARINDEX( '-'
, ot1.Full_Order_No
, PATINDEX( '%[0-9]-[0-9]%', ot1.Full_Order_No) + 2) - (PATINDEX( '%[0-9]-[0-9]%', ot1.Full_Order_No) + 2))
-- Simplifying the previous code using CROSS APPLY
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM #OrderTable1 ot1
CROSS APPLY (SELECT PATINDEX( '%[0-9]-[0-9]%', ot1.Full_Order_No) + 2 AS OrderPos) op
INNER JOIN #OrderTable2 ot2 ON ot2.Order_No = SUBSTRING( ot1.Full_Order_No
, op.OrderPos
, CHARINDEX( '-', ot1.Full_Order_No, op.OrderPos) - op.OrderPos)
--Using the DelimitedSplit8k
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM #OrderTable1 ot1
CROSS APPLY dbo.DelimitedSplit8K( ot1.Full_Order_No, '-') s
INNER JOIN #OrderTable2 ot2 ON ot2.Order_No = s.Item AND s.ItemNumber = 3
GO
--Cleaning my sandbox
DROP TABLE #OrderTable1
DROP TABLE #OrderTable2
May 11, 2015 at 1:51 pm
Edit: In your original, change the INNER JOIN condition to this:
INNER JOIN OrderTable2 ot2 ON ot2.Order_No = SUBSTRING(ot1.Full_Order_No, 10, CHARINDEX('-', SUBSTRING(ot1.Full_Order_No, 10, 20)) - 1)
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply