Forum

This content is now out of date.

Visit Our Community

Stripping out part of a string

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

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:


Forum image


[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:


Forum image


After:


Forum image


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
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
Hi Dustin

I found the syntax for Oracle - SUBSTR (string, 1, length (string) -16) - and it worked a treat.

Thanks and regards

Paul

Thanks for trying Yellowfin

Please complete the form below to request your copy of Yellowfin today.