Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
techvarun
Valued Contributor II

Extract text between special characters

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

8 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Extract text between special characters

Try something like this:

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

- Marcus

Highlighted
Partner
Partner

Re: Extract text between special characters

Hi,

Try:

TextBetween(VALUES,'- ',' (')as "VALUES"
Highlighted
Partner
Partner

Re: Extract text between special characters

Use the textbetween function like this

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

Load * Inline [Text

'11 (123456) - Automotive (ABI)'

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

];

Highlighted
MVP & Luminary
MVP & Luminary

Re: Extract text between special characters

Hi,

Try this

LOAD

*,

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

FROM Datasource;

Regards,

Jagan.

Highlighted
Partner
Partner

Re: Extract text between special characters

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'

Highlighted
Not applicable

Re: Extract text between special characters

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"

Highlighted
MVP & Luminary
MVP & Luminary

Re: Extract text between special characters

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

- Marcus

Highlighted
Not applicable

Re: Extract text between special characters

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;