Extract text between special characters

[techvarun]

Hi Qlikxperts,

I have values like below  and I want to extract only the Text in between - and (

11 (123456) - Automotive (ABI)

AX112(123456) - British Airways (BAY)

I need Automotive for row1 and  British Airways for row2

Thanks

Varun

[marcus_sommer]

Try something like this:

trim(subfield(subfield(Text, '-', 2),'(', 1))

- Marcus

[fvelascog72]

Hi,

Try:

TextBetween(VALUES,'- ',' (')

as "VALUES"

[stigchel]

Use the textbetween function like this

Load TextBetween(Text,'- ','(') as NewText;

Load * Inline [Text

'11 (123456) - Automotive (ABI)'

'AX112(123456) - British Airways (BAY)'

];

[jagan]

Hi,

Try this

LOAD

*,

Trim(Replace(TextBetween(FieldName, ')', '('), '-', '')) AS NewFieldName

FROM Datasource;

Regards,

Jagan.

[awhitfield]

Hi Techvarun

TextBetween(Your Filed ,'- ', '(') AS Newfield

Regards

Andy

TextBetween(s , beforetext , aftertext [, n ])

Returns the text between the n:th occurrence of beforetext and the immediately following occurrence of aftertext within the string s.

Examples:

TextBetween('<abc>', '<', '>') returns 'abc

>TextBetween('<abc><de>', '<', '>',2) returns 'de'

[#7]

Hi

Is there a way of carrying this same function out when loading from SQL data base?  I cant seem to get it working!

"ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'TextBetween' is not a recognized built-in function name.

SQL SELECT*,

Trim(Replace(TextBetween(MeetingDateTime, ')', '('), '-', '')) AS PkDate"

[marcus_sommer]

You need to place these functionality within a preceeding load, see here what is meant: Preceding Load.

- Marcus

[#9]

TableName1:

SQL SELECT * FROM Table;

TableName2:

LOAD

          *,

          trim(subfield(subfield(YourField, 'The-Start-Deliminator', 2[Subfield Number]),'The-End-Deliminator', 1)) //This is a QV function, not a SQL function.

RESIDENT TableName1;

DROP TABLE TableName1;