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
  • 732,580
  • 175
  • 1,330
  • 1,459
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
  • 64,414
  • 37
  • 100
  • 175
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,637
  • 3
  • 20
  • 43
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
  • 609
  • 1
  • 10
  • 27
  • 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
  • 79,615
  • 7
  • 44
  • 66