Post reply

Expression to TRIM

  • April 1, 2015 at 3:18 pm

    #295633

    Hello

    I'm having some data coming through as:

    Mr John Smith (12, Livia Close, Anytown, 125765)

    Can anyone advise on an expression on how I can remove the 'Mr John Smith' part and also the brackets around the address? I would like to just have:

    12, Livia Close, Anytown, 125765

    Any advice appreciated. Thanks.

  • April 1, 2015 at 3:26 pm

    #1788146

    Something like...

    =Mid(ReportItems!txtNameAddress.Value,InStr(1,ReportItems!txtNameAddress.Value,"(")+1,Len(ReportItems!txtNameAddress.Value)-InStr(1,ReportItems!txtNameAddress.Value,"(")-1)

    ?

  • April 1, 2015 at 8:12 pm

    #1788173

    You could also modify your stored proc (or ad hoc query) that feeds the dataset to format the data before it gets to SSRS. Your query would look something like this (I'm use a variable in place of a column name):

    DECLARE @x varchar(100) = 'Mr John Smith (12, Livia Close, Anytown, 125765)';

    SELECT SUBSTRING(@x,charindex('(',@x)+1,charindex(')',@x)-charindex('(',@x)-1);

    I personally prefer to handle stuff like this in SQL Server because more people understand T-SQL than understand SSRS expressions.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • April 2, 2015 at 12:59 am

    #1788188

    Dear pietlinden

    You got me pointed in the right direction, many thanks. I used:

    =Mid(Fields!Address.Value, InStr(Fields!Address.Value, "(") + 1, InStr(Fields!Address.Value, ")") - InStr(Fields!Address.Value, "(") - 1)

  • April 2, 2015 at 1:02 am

    #1788189

    Dear Alan.B

    Yes, I should work towards what you suggested. I daresay the query would run faster as well. Thanks for taking the trouble to reply.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply