May 24, 2011 at 8:48 am
I have a table with an order description, order number, and order line in one column.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#OrderNum','U') IS NOT NULL DROP TABLE #OrderNum
GO
--===== Create the test table
CREATE TABLE #OrderNum
(OrderNumLine NVARCHAR(30))
--===== Insert the test data into the test table
INSERT INTO #OrderNum
(OrderNumLine)
SELECT 'INV PNI 1520527 1' UNION ALL
SELECT 'INV PNI 1265765 22' UNION ALL
SELECT 'INV PNI 6543 3' UNION ALL
SELECT 'INV PNI 32456 17' UNION ALL
SELECT 'INV PNI 2804269 8' UNION ALL
SELECT 'INV PNI 1765309 28' UNION ALL
SELECT 'INV PNI 313497527 4' UNION ALL
--==== SELECT the records
SELECT OrderNumLine FROM #OrderNum
I want to pull out only the order number. It should look like this:
OrdNumLine
-----------
1520527
1265765
6543
32456
2804269
1765309
313497527
What is the best way to do this?
IF you need any more information let me know.
Thanks.
Steve
May 24, 2011 at 9:38 am
Short answer: you don't want your data to sit in a single column. Each column MUST contain one and only one information.
Longer answer: you can use a split function to extract data from an overloaded column. See this great article by Jeff Moden:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Hope this helps
Gianluca
-- Gianluca Sartori
May 24, 2011 at 9:44 am
Unfortunately, the data is contained in a table tat is part of a proprietary ERP system, and thus can not be modified.
May 24, 2011 at 10:02 am
I guess you will have to use Jeff's code to extract a substring, then.
Try it: performs very well.
-- Gianluca Sartori
May 24, 2011 at 10:32 am
Gianluca is right -- you'll have to use a substring function to extract the data. Using the tally table is blazing fast and elegant, but it may be overkill for what you need.
If your data really does come in as cleanly as your sample given here (where the OrderNum always starts at position 12), the function would look something like this:
select SUBSTRING(OrderNumLine,12,CHARINDEX(' ',OrderNumLine,13)-12) as OrderNum from #OrderNum
Or, you could get a little more fancy and add a computed column to your table specification and do this:
CREATE TABLE #OrderNum
(
OrderNumLine varchar(30) NOT NULL,
OrderNum AS SUBSTRING(OrderNumLine,12,CHARINDEX(' ',OrderNumLine,13)-12)
)
GO
--===== Insert the test data into the test table
INSERT INTO #OrderNum (OrderNumLine)
SELECT 'INV PNI 1520527 1' UNION ALL
SELECT 'INV PNI 1265765 22' UNION ALL
SELECT 'INV PNI 6543 3' UNION ALL
SELECT 'INV PNI 32456 17' UNION ALL
SELECT 'INV PNI 2804269 8' UNION ALL
SELECT 'INV PNI 1765309 28' UNION ALL
SELECT 'INV PNI 313497527 4'
--==== SELECT the records
SELECT * FROM #OrderNum
drop table #OrderNum
Rob Schripsema
Propack, Inc.
May 24, 2011 at 10:37 am
Rob,
Thanks for the code. That will work in this case.
Gianluca,
Thank you for the article. I have it bookmarked to read and reference. I will be needing this in about 3 weeks.
May 24, 2011 at 10:43 am
Rob Schripsema (5/24/2011)
Using the tally table is blazing fast and elegant, but it may be overkill for what you need.
Indeed. Nice one, Rob.
-- Gianluca Sartori
May 24, 2011 at 12:10 pm
And assuming that the order number won't always start at the same position, this will handle extracting it.
;WITH cte AS
(
-- get the position of the first numeric character
SELECT OrderNumLine, pos = PATINDEX('%[0-9]%', OrderNumLine)
FROM #OrderNum
)
-- get the position of the first space after the order number starts
-- get the string between those two positions
SELECT OrderNumLine,
OrderNum = SUBSTRING(OrderNumLine, pos, CHARINDEX(' ', OrderNumLine, pos) - pos)
FROM cte;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply