How to extract part of a string?

Question

How to extract part of a string?

Goalie35

SSC Eights!

Points: 871
More actions
May 11, 2015 at 11:10 am

#296724

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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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://www.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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101134 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".