Solved

Parse a substring of characters to the left of a period

Posted on 2004-10-29
316 Views
Last Modified: 2006-11-17
I am trying parse a substring of characters to the left of a period. I need to perfom a select statement that will parse a substring of data to the left of a period. The sql statement is:
SELECT     dbo.OrderHeader.PrimaryCustomerID, dbo.OrderHeader.OrderNumber, dbo.OrderLine.EventDateToDeliver, dbo.LineDetail.Weight,
                      dbo.OrderHeader.Remarks
FROM         dbo.OrderHeader INNER JOIN
                      dbo.OrderLine ON dbo.OrderHeader.ID = dbo.OrderLine.OrderHeaderID INNER JOIN
                      dbo.LineDetail ON dbo.OrderLine.ID = dbo.LineDetail.OrderLineID
where dbo.LineDetail.PO = '929477'

The output is:
PrimaryCustomerID    OrderNumber                     EventDateToDeliver             Weight       Remarks
102                          6061 - EXP 2.1014074546      2004-10-19 21:00:00.000          96.0        NULL
102                          6061 - EXP 2.1014074546      2004-10-19 21:00:00.000          148.0       NULL

The field that I need to parse is OrderNumber. The only thing that is constant is the period.
0
Question by:infutech
    7 Comments
     
    LVL 68

    Accepted Solution

    by:
    For the chars before the '.':

    SELECT ..., LEFT(OrderNumber, CHARINDEX('.', OrderNumber) - 1)


    For the chars after the '.':

    SELECT ..., SUBSTRING(OrderNumber, CHARINDEX('.', OrderNumber) + 1, LEN(OrderNumber))


    0
     
    LVL 13

    Expert Comment

    by:MikeWalsh
    When you say parse, do you mean parse or do you mean crop? Are you trying to only return what is to the left of the period or what is to the right of the period?

    show an example of a row you would like returned.
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    this also makes sure that it works if there is not . - if that can never happen, just do:


    (
            case when charindex('.', dbo.OrderHeader.OrderNumber) > 0
                    then Left(dbo.OrderHeader.OrderNumber, charindex('.', dbo.OrderHeader.OrderNumber) - 1)
                    else null
              end
        ) as ORDERBNUMBERLEFT


    0
     
    LVL 13

    Expert Comment

    by:MikeWalsh
    or nevermind, I could have given you both like scott did :)
    0
     
    LVL 12

    Expert Comment

    by:pique_tech
    What part of the OrderNumber do you need to keep or process?  What you're asking isn't difficult, but can't be specifically addressed without better understanding what you need to parse and process.
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    or this if you want the order number when there is no period

    (
            case when charindex('.', dbo.OrderHeader.OrderNumber) > 0
                    then Left(dbo.OrderHeader.OrderNumber, charindex('.', dbo.OrderHeader.OrderNumber) - 1)
                    else dbo.OrderHeader.OrderNumber
              end
        ) as ORDERBNUMBERLEFT

    0
     

    Author Comment

    by:infutech
    In my example above the Ordernumber is:  6061 - EXP 2.1014074546  I only need: 6061 - EXP 2  Retruned to me.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    Suggested Solutions

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now