--example
select
left(string,charindex('-',string) ) A
, left(string,charindex('-',string+'-') ) B
FROM ( VALUES('some-text'),('some text') ) AS test_data(string)
+-------+-----------+
| A | B |
+-------+-----------+
| some- | some- |
| | some text |
+-------+-----------+
SELECT dbo.joitem.fjobno, dbo.joitem.fpartno, dbo.joitem.fdesc, dbo.jomast.fstatus
FROM dbo.joitem INNER JOIN
dbo.jomast ON dbo.joitem.fjobno = dbo.jomast.fjobno
WHERE (dbo.jomast.fstatus = 'RELEASED')
SELECT ji.fjobno, ji.fpartno, ji.fdesc, jm.fstatus, SUBSTRING(CAST(ji.fdesc AS varchar(max)), ca1.starting_byte, ca2.length)
AS numeric_value
/*<<-- chg col name to any name you want*/ FROM dbo.joitem AS ji INNER JOIN
dbo.jomast AS jm ON ji.fjobno = jm.fjobno CROSS APPLY (
SELECT PATINDEX('%[-][A-Z][A-Z][-][0-9]%[-]%', CAST(ji.fdesc AS varchar(max)) + '-') + 4 AS starting_byte
) AS ca1
CROSS APPLY (
SELECT PATINDEX('%[^0-9.]%', SUBSTRING(CAST(ji.fdesc AS varchar(max)) + '-', starting_byte, 100)) - 1 AS length
) AS ca2
WHERE (jm.fstatus = 'RELEASED') AND ji.fprodcl <> '03'
select * from fn_txt_split (col, '-')
Create function (not mine, just one I found):
Open in new window