Search code examples
casespotfire

Tibco Spotfire - Issue with text functions

I have a column that is prefixed with OIR$ then a last name. How do I get rid of the OIR$?

I've tried using RIGHT() but the names are of different lengths, so if I use Right([column.name], 10) then I will get some names correct and others will still have the unwanted letters of OIR$.

Solution

  • Maybe:

    SUBSTRING([column], 5, LEN([column]))

RIGHT([column], LEN([column]) - CHARINDEX('$', [column]))

REPLACE([column], 'OIR$', '')

    e.g.

    declare @x varchar(20);
SELECT @x = 'OIR$testing';

SELECT SUBSTRING(@x, 5, LEN(@x))
testing

SELECT RIGHT(@x, LEN(@x) - CHARINDEX('$', @x))
testing

SELECT REPLACE(@x, 'OIR$', '')
testing

    EDIT: It's not SQL. Maybe https://docs.tibco.com/pub//spotfire/5.5.0-march-2013/UsersGuide/ncfe/ncfe_text_functions.htm

    RXReplace([column], "OIR$", "", "")
Mid([column], 5, Len(column))