-1

The order number column in the jobs table of the SQL database has the following structure: "2006925-06/01".

The string value between the "-" and the "/" denotes the sales order line that links to the job order number in the jobs table. I'm trying to use the following statement to extract the values sales order line value and present it as PulledString. When I execute the statement I get an errorL

Invalid length parameter passed to the LEFT or SUBSTRING function.

Not sure what's wrong?

  , CASE WHEN [ordernumber] Is Not Null THEN 
    SUBSTRING([ordernumber],CHARINDEX('-', [ordernumber], CHARINDEX('-', [ordernumber]) + 1) -0,
        LEN([ordernumber]) - CHARINDEX('/', [ordernumber], CHARINDEX('/', [ordernumber]) + 1) - 
            CHARINDEX('-', REVERSE(rtrim([ordernumber])))) ELSE '' END AS PulledString
Dale K
  • 25,246
  • 15
  • 42
  • 71
RickC
  • 9
  • 2
  • 2
    Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|192.6778). That applies to different delimiters as well. Then normalize the schema. The job number should be in its own column, alone, with nothing else. – sticky bit May 05 '20 at 01:30

1 Answers1

0

If is is always two characters, this is simple enough:

select left(stuff(ordernumber, 1, charindex('-', ordernumber), ''), 2)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, Gordon. I added that code to a Case statement and was able to strip out and store the sales order line value in a column. The sales order line should be no more than two characters in length. The sales order line in the sales order table is stored as an integer value. I'm thinking that if I trim leading zero when the extracted job order value is between 01 and 09, I should have a good chance of matching the sales order line on which a job number appears. – RickC May 05 '20 at 10:39
  • @Rick . . . Actually, you can use `like` for the `join`, which is much simpler. But that is not the question that you asked here. If you provide sample data, desired results, and an explanation of what you want to do, then there is more flexibility in arriving at the best solution. This answers the question that you asked here. – Gordon Linoff May 05 '20 at 12:32
  • "2006847-06/01". Is the work order (job) string. The 06 in positions 9&10 represent the line of the sales order it came from. Sales orders range from 1 to 99 lines. Using my query below (no issues with joins) if I run for a work order 2006847-10/11, I get the expected # of rows pertaining to the number of operations. If I run for work order 2006847-06/01, nothing is returned. It seems to me that the string value 09 does not compare to the sales order line number 09. See the next block for the joins and where clauses. – RickC May 05 '20 at 17:17
  • FROM dbo.WorkOrder INNER JOIN [SalesOrder] ON [SalesOrder].SalesOrderID = [WorkOrder].SalesOrderID OR SUBSTRING([WorkOrder].OrderNumber, 1, LEN(SalesOrder.OrderNumber)) = SalesOrder.OrderNumber INNER JOIN [SalesOrderLine] ON [SalesOrderLine].SalesOrderID = [SalesOrder].SalesOrderID and item.itemid = SalesOrderLine.ItemID and SUBSTRING([WorkOrder].OrderNumber, 9, LEN(2)) = [SalesOrderLine].[LineNo] INNER JOIN [SalesOrderDelivery] ON [SalesOrderLine].SalesOrderLineID = [SalesOrderDelivery].SalesOrderLineID WHERE [WorkOrder].ordernumber = '2006847-10/11' – RickC May 05 '20 at 17:18