The existing row on the left is in SQL, I want to create a table inside of SQL applying the following excel formula to it:

I'm trying to apply a formula grabbing text to the right of the last dash in column 'PO#'

**=IFERROR(RIGHT([@[PO'#]],LEN([@[PO'#]])-SEARCH("$",SUBSTITUTE([@[PO'#]],"-","$",LEN([@[PO'#]])-LEN(SUBSTITUTE([@[PO'#]],"-",""))))), [@[PO'#]])**

Normalized output

CodePudding user response：

Why not something like this ... Note the "Fail-Safe" ... '-'

Select right(SomeCol,charindex('-',reverse(SomeCol) '-')-1)

CodePudding user response：

Please try the following solution based on tokenization.

Method #2 as an alternative method.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(1000));
INSERT INTO @tbl (tokens) VALUES 
('1333-fd-fdd'),
('12345'),
('BRASIL'),
('1122-18-fdl'),
('1122-18-fia');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '-';

-- Method #1
SELECT t.*
   , c.value('(/root/r[last()]/text())[1]', 'VARCHAR(100)') AS Result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
      REPLACE(tokens, @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML)) AS t1(c);

-- Method #2
SELECT t.* --, pos
   , RIGHT(tokens, pos) AS Result
FROM @tbl AS t
    CROSS APPLY (SELECT CHARINDEX(@separator, REVERSE(tokens)) - 1) AS t1(_pos)
    CROSS APPLY (SELECT IIF(_pos = -1, LEN(tokens), _pos)) AS t2(pos);

Output