Split delimiter

[ttal7878]

Hi Community,

I have a column (Site) that contains fields like "Qatar_7-newsuk-thesun",

I was asked to display  2 new column (column A + column B)  by dividing column "Site"  by delimiter "-"

The division will be until the first dash

The final result should be:

the data displayed in column A : Qatar_7

the data displayed in column B : newsuk-thesun

I have attached an example qvw

[Miguel_Angel_Baeyens]

Using

...

SubField(Site, '-', 1) AS ColumnA,

SubField(Site, '-', 2) AS ColumnB,

...

Will work if the data is consistent, i.e.: no misplaced "-" that you don't want to filter in that exact position

[ttal7878]

Thank Miguel for your replay,

But my intention is, the division will be up to the first dash, and then to display the information as it is,

for example:

Qatar_7-newsuk-thesun-

Column A: Qatar_7

Column B: newsuk-thesun

[Miguel_Angel_Baeyens · Accepted solution]

Use instead for example:

Mid(SIte, Index(SIte, '-', 1)+1) AS ColumB

[Anil_Babu_Samineni]

Can you provide expected result set from this row? Chile_Desktop34-msn-outlook-chile

[ttal7878]

Thank you!

[ttal7878]

mbaeyens‌

Is it possible that when the field does not contain "-", a blank value will be inserted into column B?

For example, the field "VE_30" doesn't contain "-" , therefore a blank value will enter column B

[Miguel_Angel_Baeyens]

Yes, you will need to add a conditional that if the expression above so if the Len() of the result = 0, then ' ', otherwise Mid(...)

[Miguel_Angel_Baeyens]

Or with a proper syntax:

If(Len(Mid(SIte, Index(SIte, '-', 1)+1) = 0, ' ', Mid(SIte, Index(SIte, '-', 1)+1)) AS ColumB

[ttal7878]

It was not exactly what I meant, but I managed to find a solution.

many thanks mbaeyens‌

if(Index(SIte,'-'),Mid(SIte, Index(SIte, '-', 1)+1),'') AS ColumnB