• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

Parse a substring of characters to the left of a period

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
infutech
Asked:
infutech
1 Solution
 
Scott PletcherSenior DBACommented:
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
 
MikeWalshCommented:
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
 
sigmaconCommented:
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
MikeWalshCommented:
or nevermind, I could have given you both like scott did :)
0
 
pique_techCommented:
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
 
sigmaconCommented:
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
 
infutechAuthor Commented:
In my example above the Ordernumber is:  6061 - EXP 2.1014074546  I only need: 6061 - EXP 2  Retruned to me.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now