Need To Pull Data from the Middle of a Column

  • 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

  • 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

  • Unfortunately, the data is contained in a table tat is part of a proprietary ERP system, and thus can not be modified.

  • I guess you will have to use Jeff's code to extract a substring, then.

    Try it: performs very well.

    -- Gianluca Sartori

  • 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.

  • 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.

  • 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

  • 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


    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

Viewing 8 posts - 1 through 7 (of 7 total)

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