Stripping out part of a string
25 June, 2015
I am trying to create an advanced sub-query by joining across two databases.
Database 1 contains a receipt number for each transaction - eg 321. Each transaction is passed across to database 2, but database 2 adds a timestamp to the end of the receipt number. For example, receipt 321 becomes 3212015062511253015 in database 2.
Receipt numbers vary in length, but the time stamp does not of course. Is there a way to create a field that drops the last 16 digits of the database 2 receipt number so it matches the figure in database 1?
I am not a techie, so freehand SQL is beyond me unless you write it for me.
Thanks and regards
Paul
You could use the SUBSTRING() SQL function to do this however it would require the use of a calculated field as seen below:

[code]substring(string, 1, (length(string)-16))[/code]
This calculated field counts the length of the original field and removes 16 characters from the end.
Before:

After:

The length() function used is syntax specific and depends on which database you are using. The example above was written in postgreSQL so you might need to do some research regarding database specific syntax.
Please let me know if this information was helpful. Have a great day!
Cheers,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
Thanks very much for the response. I do not seem to have received an email to say you had responded; hence my slow response to you. I will have a go using your prescription.
Regards
Paul
We look forward to hearing how it goes.
Cheers,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
I found the syntax for Oracle - SUBSTR (string, 1, length (string) -16) - and it worked a treat.
Thanks and regards
Paul