October 5, 2011 at 3:34 pm
I have a table that looks like this:
flowname
UPDATE: PurchaseOrderLines 104142-2-0
UPDATE: PurchaseOrderLines 110505-2-0
UPDATE: PurchaseOrderLines 111900-5-0
UPDATE: PurchaseOrderLines 113507-2-0
It can be built here:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#PurchaseOrder','U') IS NOT NULL DROP TABLE #JobOrder
GO
--===== Create the test table
CREATE TABLE #PurchaseOrder(
FlowNameNVARCHAR(1000),
)
--===== Insert the test data into the test table
INSERT INTO #PurchaseOrder
(FlowName)
SELECT 'UPDATE: PurchaseOrderLines 104142-2-0' UNION ALL
SELECT 'UPDATE: PurchaseOrderLines 110505-2-0' UNION ALL
SELECT 'UPDATE: PurchaseOrderLines 111900-5-0' UNION ALL
SELECT 'UPDATE: PurchaseOrderLines 113507-2-0'
--==== SELECT the records
SELECT FlowName FROM #PurchaseOrder
The first set of numbers after the words are purchase order numbers (104142, 110505, 111900, 113507). The first numbers after the dashes are purchase order line numbers (2, 2, 5, 2). I need to extract this information to look like this:
PONumPOLine
-----------
1041422
1105052
1119005
1135072
What is the best way to do this? Let me know if you need any more information.
Thanks.
Steve
October 5, 2011 at 5:25 pm
Now this will do what you say you need, BUT note that it will NOT correctly handle
1. Line numbers greater that a single digit
2. Purchase order numbers > 6 digits
but hopefully this will give you a start.
SELECT SUBSTRING(Flowname,CHARINDEX(' ',Flowname,CHARINDEX (' ' ,Flowname,1)+2),7) AS 'P.O. Num'
,SUBSTRING(Flowname,CHARINDEX('-',Flowname)+1,1) AS 'Line #'
FROM #PurchaseOrder
Result:
P.O. Num Line #
104142 2
110505 2
111900 5
113507 2
October 5, 2011 at 5:39 pm
Yes, that will get me started. I should have mentioned that the purchase order numbers may vary in size, as well as the line numbers, which may be from one to three characters in length.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply