3

Got dbo where i need to split it into two, after first '-' character. Working on SSMS 2014

example in spreadsheet: example

PartNumber holds data which needs to be break up. Part - Need to have all characters before first '-' Number - need to have all characters after first '-'

Any help appreciated thanks

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
hyprex
  • 41
  • 2

4 Answers4

0

You need LEFT and RIGHT. And to find the location where you want to split to LEFT and RIGHT, us CHARINDEX.

Lajos Arpad
  • 53,986
  • 28
  • 88
  • 159
0

Maybe something like this?

SELECT parts.PartID as ID,
Part = (SELECT TOP 1 value FROM STRING_SPLIT(parts.PartNumber, '-')),
Number = (SELECT value FROM STRING_SPLIT(parts.PartNumber, '-') LIMIT 1 OFFSET 1),
FROM dbo.PartsTable parts
FailedUnitTest
  • 1,403
  • 1
  • 21
  • 33
0

You could try this.

SELECT 
    PartNum
,   REPLACE(LEFT(PartNum,  CHARINDEX('-', PartNum)),'-', '') as 'PartNum First'
,   REPLACE(SUBSTRING(PartNum,CHARINDEX('-', PartNum), LEN(PartNum)), '-','') as 'PartNum Second'

FROM Part

The query above splits the PartNum string when it finds '-', it then replaces it with a blank space so you have the result you expected.

I tried it and it works. Hope it's useful to you.

gmwill934
  • 469
  • 9
  • 25
  • Hi, thanks for your help. – hyprex Oct 23 '16 at 15:40
  • It works, but not fully. It takes all before first '-' from left and put this into the column. Then second line takes rest and put into second column..but in 2nd part is '-' it will remove and join. – hyprex Oct 23 '16 at 15:43
0
Declare @YourTable table (PartNumber varchar(50))
Insert Into @YourTable values 
('HHY-12-1800-2'),
('FC-P-41-4')


Select PartNumber
      ,Part   = Left(PartNumber,CharIndex('-',PartNumber)-1)
      ,Number = Substring(PartNumber,CharIndex('-',PartNumber)+1,Len(PartNumber))
 From  @YourTable

Returns

PartNumber      Part    Number
HHY-12-1800-2   HHY     12-1800-2
FC-P-41-4       FC      P-41-4
John Cappelletti
  • 71,300
  • 6
  • 42
  • 62