Stripping out part of a string
25 June, 2015
Hi all
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
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
Hi 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
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
Hi Dustin
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
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
No Worries 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
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
Hi Dustin
I found the syntax for Oracle - SUBSTR (string, 1, length (string) -16) - and it worked a treat.
Thanks and regards
Paul
I found the syntax for Oracle - SUBSTR (string, 1, length (string) -16) - and it worked a treat.
Thanks and regards
Paul