0

Let's say I have a bunch of part numbers, and I want a column to return the string after the first period and before the second period. I looked at other similar questions but couldn't figure it out

So let's say I have of list of part numbers such as:

10416.1.1.4
10416.1.1.7
10416.1.1.1
10416.2.3
10416.2.2
10416.3.1.2
10416.3.1.3
10416.4.1.1
10416.10.1
10416.10.2
10416.11.1.1

I should have my column return:

1
1
1
2
2
3
3
4
10
10
11

Using SQL Server 2012, thanks in advance

Edit: Here's my code, this returns a table but the sorting is all over the place, the PartNo column is what I'm trying to split

SELECT DISTINCT OrderDet.OrderNo, Scheduling.JobNo, OrderDet.PartNo, 
OrderDet.Priority
FROM Scheduling LEFT JOIN OrderDet ON Scheduling.JobNo = OrderDet.JobNo
WHERE Scheduling.WorkCntr = 'Glazing'
ORDER BY OrderDet.Priority DESC
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
user1452574
  • 485
  • 1
  • 6
  • 15
  • What have you tried that doesn't work? If you haven't tried anything, you may want to google "SQL Server TSQL string handling functions". It looks like you may need to know how to find a substring within a string, as well as how to extract a substring from a string. – STLDev May 04 '17 at 22:25
  • Possible duplicate of [A SQL Query to select a string between two known strings](http://stackoverflow.com/questions/18362260/a-sql-query-to-select-a-string-between-two-known-strings) – Cᴏʀʏ May 04 '17 at 22:29
  • Edited my post to add my base code. Haven't really tried much, I know how basic string functions work like left, substring, but not sure how to extract specific data yet. Having so many periods in my part numbers doesn't help – user1452574 May 04 '17 at 22:48

1 Answers1

5

You can use ParseName() in concert with Reverse()

Declare @YourTable Table ([SomeCol] varchar(50))
Insert Into @YourTable Values
 ('10416.1.1.4')
,('10416.1.1.7')
,('10416.1.1.1')
,('10416.2.3')
,('10416.2.2')
,('10416.3.1.2')
,('10416.3.1.3')
,('10416.4.1.1')
,('10416.10.1')
,('10416.10.2')
,('10416.11.1.1')

Select * 
      ,NewCol = reverse(ParseName(reverse(SomeCol),2))
from @YourTable

Returns

SomeCol         NewCol
10416.1.1.4     1
10416.1.1.7     1
10416.1.1.1     1
10416.2.3       2
10416.2.2       2
10416.3.1.2     3
10416.3.1.3     3
10416.4.1.1     4
10416.10.1      10
10416.10.2      10
10416.11.1.1    11
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66