CODEWITHSUNDEEP
sqlsql-server
user11990421
user11990421

Reputation: 1

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

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

enter image description here

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.

Upvotes: 0

Views: 57

Answers (3)

Philip Kelley
Philip Kelley

Reputation: 40319

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

Upvotes: 0

Rajat
Rajat

Reputation: 5803

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;

Upvotes: 0

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

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

Upvotes: 1

Related Questions