SQL Server: How to keep track of CURRENT occurrence of A substring in a string

Question

I have two columns, PO_NUMBER and PO_COUNTER. PO_NUMBER column has many PO numbers. For example, 601A-00001, 601A-00002, 601A-00003....601A-00101. I need the PO_COUNTER column to count the number of PO's in each project. The project numbers are the digits to the left of '-' and the count should start back at 1 when their is a different project number in the PO_NUMBER. The screenshot pasted below is how the final result set should look...

I have tried using SUBSTRING and CHARINDEX.....

select PO_number, RIGHT(PO_number, CHARINDEX('0', REVERSE(PO_Number))-1)
from Purchase_Order
order by PO_NUMBER

but when there is PO_NUMBER 602A-00105 and 601A-00101 like shown above, my code returns 5 and 1 because those are the digits after the last occurrence of 0.

I have also tried...

select PO_number, SUBSTRING(po_number, CHARINDEX('-', po_number) + 1, LEN(po_number) - CHARINDEX('-', po_number)) domain
from Purchase_Order
order by PO_NUMBER

but this returns everything after the '-' and I don't want that.

Use the second option and trim the zeroes as in https://stackoverflow.com/a/662437/3770885 — Reema, Aug 28 '19 at 17:18
Look for the hyphen and not zeroes. As is you can just strip off the last five characters. — shawnt00, Aug 28 '19 at 18:23
Isn't it the case that you want everything BEFORE the '-'? And if it is, why couldn't you modify your second script to get it? — Tab Alleman, Aug 28 '19 at 18:41

score 1 · Answer 1 · answered Aug 28 '19 at 17:48

1

Try make the following change:

select PO_number, try_convert(int, RIGHT(PO_number, CHARINDEX('-', REVERSE(PO_Number))-1))
from Purchase_Order
order by PO_NUMBER

answered Aug 28 '19 at 17:48

Neeraj Agarwal

1,059
6
5

Radagast · Answer 2 · 2019-08-28T20:18:37.810

0

If the counting suffix is always in 00000 format, then you could extract just that and get rid of the leading zeroes by casting it as integer.

select PO_number, cast(right(PO_number,5) as int)
from Purchase_Order
order by PO_number;

edited Aug 28 '19 at 20:18

answered Aug 28 '19 at 20:12

Radagast

5,102
3
12
27

score 0 · Answer 3 · answered Aug 28 '19 at 20:58

The following makes a few assumptions:

You are using a version of SQL Server that supports the ranking functions
Every value in PO_Number contains at least one hyphen, and one or more characters before the first hyphen
This counts the project numbers, it does not extract them the PO number... or how did you want to treat gaps in the data?

SELECT
   PO_Number
  ,row_number() over (partition by left(PO_Number, charindex('-', PO_Number) - 1)  order by PO_Number)  PO_Counter
 from Purchase_Order
 order by PO_Number, PO_Counter

SQL Server: How to keep track of CURRENT occurrence of A substring in a string

3 Answers3