Parsing text fields
18 January, 2010
I have a data field which is a text structure of a number of sub-fields. Is there a way I can parse the text, thereby pulling out the fields? I thought I could use a SUBSTRING command in a Freehand SQL command, but I'm having issues constructing it.
An example of the text is:
[quote]218Vivotech:StoreInfo227Insomnia 20 Upper Baggot St219Vivotech:TerminalId2167000000000003362224Vivotech:TransactionType214Loyalty Earned[/quote]
From which I would parse out StoreInfo, TerminalId and TransactionType.
Do you think it is worth attempting this using Freehand SQL within Yellowfin or in the DB itself? I would prefer within Yellowfin myself.
An example of the text is:
[quote]218Vivotech:StoreInfo227Insomnia 20 Upper Baggot St219Vivotech:TerminalId2167000000000003362224Vivotech:TransactionType214Loyalty Earned[/quote]
From which I would parse out StoreInfo, TerminalId and TransactionType.
Do you think it is worth attempting this using Freehand SQL within Yellowfin or in the DB itself? I would prefer within Yellowfin myself.
This is best done at the database level. The overhead for Yellowfin to parse this sort of data on the fly is considerable, and depending exactly how you want to extract the data, may be close to impossible. We would recommend a process that extracts this data into another table, which one column for each piece of data that you are interested in.
Having said that, it should be possible to extract data from this field if you know the order of the fields. The sql functions to do this will depend on which database you are using. For MS SQL Server you can use the SUBSTRING function. Assuming that the fields are of variable length, you will also need to find the start and end index of the field within the string using CHARINDEX.
An example to retrieve the second token:
[quote]SUBSTRING(TextField, CHARINDEX(':', TextField) + 1, CHARINDEX(':', TextField, CHARINDEX(':', TextField) + 1) - CHARINDEX(':', TextField) - 1)[/quote]
As you can see, this is already fairly complicated. If you have a variable number of fields, or you aren't sure of the location of the fields within the string, it will be even more difficult.
Having said that, it should be possible to extract data from this field if you know the order of the fields. The sql functions to do this will depend on which database you are using. For MS SQL Server you can use the SUBSTRING function. Assuming that the fields are of variable length, you will also need to find the start and end index of the field within the string using CHARINDEX.
An example to retrieve the second token:
[quote]SUBSTRING(TextField, CHARINDEX(':', TextField) + 1, CHARINDEX(':', TextField, CHARINDEX(':', TextField) + 1) - CHARINDEX(':', TextField) - 1)[/quote]
As you can see, this is already fairly complicated. If you have a variable number of fields, or you aren't sure of the location of the fields within the string, it will be even more difficult.