I have a database field that is VARCHAR with data stored in the following manner:

DatabaseField
1185731-2148838B
1185731-2148838S
1185731-2148838W
1185731-2148839B

I would like to separate this field into two separate columns - containing the numerical digits on either side of the "-".

To get the values before and after the '-', I can use the following

SUBSTRING(DatabaseField,0,CHARINDEX('-',DatabaseField))
SUBSTRING(DatabaseField,CHARINDEX('-',DatabaseField) 1,LEN(DatabaseField))

Which gives me

NewColumnA   NewColumnB
1185731      2148838B
1185731      2148838S
1185731      2148838W
1185731      2148839B

How could I then drop any non-numeric characters from the new column B - and it might not be one letter in the column - it could be 2 or 3 so I cannot just drop the last digit from the new column.

CodePudding user response：

Stuff 'em!

declare @test table (DatabaseField varchar(30));

insert into @test values
  ('1185731-2148838B') 
, ('1234567-1234567?') 
, ('1185731-214883612WAH') 
, ('1185731-2148839BLAH') 
;

select DatabaseField
, [NewColumnA] = LEFT(DatabaseField, CHARINDEX('-', DatabaseField)-1)
, [NewColumnB] = PARSENAME(REPLACE(STUFF(DatabaseField, PATINDEX('%[^0-9-]%',DatabaseField),42,''),'-', '.'),1)
from @test;

Test on db<>fiddle

DECLARE @test TABLE (DatabaseField VARCHAR(30));
INSERT @test VALUES('1185731-2148838B'), ('1234567-1234567?'),
                   ('1185731-214883612WAH'), ('1185731-2148839BLAH');

SELECT
  t.DatabaseField,
  NewA = SUBSTRING(clean.S,1,f1.Pos),
  NewB = SUBSTRING(clean.S, f1.Pos 2, 30)
FROM        @test AS t
CROSS APPLY (VALUES(CHARINDEX('-', t.DatabaseField)-1))        AS f1(Pos)
CROSS APPLY (VALUES(PATINDEX('%[^0-9-]%', t.DatabaseField)-1)) AS f2(Pos)
CROSS APPLY (VALUES(SUBSTRING(t.DatabaseField,1,f2.Pos)))      AS clean(S);

DatabaseField                  NewA                           NewB
------------------------------ ------------------------------ ------------------------------
1185731-2148838B               1185731                        2148838
1234567-1234567?               1234567                        1234567
1185731-214883612WAH           1185731                        214883612
1185731-2148839BLAH            1185731                        2148839