Mid result in ssrs

Question

Mid result in ssrs

elamranii

SSC-Addicted

Points: 445
More actions
March 3, 2016 at 12:18 pm

#308966

Hi,
I am trying to retrieve a number from a field where it's between = and =
I have tried mid
=mid(Fields!PRE_LAB_VAL.Value,4,4)
but not working
PRE_LAB_VAL --------------------------Result
PT=45.7=02/07/2002-----------------45.7
RTS=51=02/07/2002------------------51
RT=26=02/07/2002--------------------26
Thank you

Viewing 15 posts - 1 through 15 (of 19 total)

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

micang SSC Eights! Points: 878 More actions · Answer 1

elamranii (3/3/2016)
Hi,
I am trying to retrieve a number from a field where it's between = and =
I have tried mid
=mid(Fields!PRE_LAB_VAL.Value,4,4)
but not working
PRE_LAB_VAL --------------------------Result
PT=45.7=02/07/2002-----------------45.7
RTS=51=02/07/2002------------------51
RT=26=02/07/2002--------------------26
Thank you

This link should help you.

http://www.sqlservercentral.com/Forums/Topic1413824-150-1.aspx

pietlinden SSC Guru Points: 63428 More actions · Answer 2

Must be getting rusty...

FirstDelim =INSTR(Fields!TwoFields.Value,"=")+1

SecondDelim = INSTRREV(Fields!TwoFields.Value,"=")

FinalValue=CDBL(MID(Fields!TwoFields.Value,Fields!FirstDelim.Value,Fields!SecondDelim.Value-Fields!FirstDelim.Value))

So without the intermediate calculated columns:

CDBL(MID(Fields!TwoFields.Value,INSTR(Fields!TwoFields.Value,"=")+1,INSTRREV(Fields!TwoFields.Value,"=") - INSTR(Fields!TwoFields.Value,"=")+1))

The CDBL just converts the text string back into a number... only necessary if you're going to do math on it or sort it in numerical order.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

If you are using SQL Serve and wanted to handle the string manipulation inside a dataset you could do something like this:

-- your data

DECLARE @strings TABLE (s varchar(100));

INSERT @strings VALUES('PT=45.7=02/07/2002'), ('RTS=51=02/07/2002'), ('RT=26=02/07/2002');

-- query for your dataset

WITH start AS (SELECT s = SUBSTRING(s, CHARINDEX('=', s)+1, 8000) FROM @strings)

SELECT s = SUBSTRING(s, 1, CHARINDEX('=',s)-1)

FROM start;

"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

elamranii SSC-Addicted Points: 445 More actions · Answer 4

I'll give it a try. thank you guys for the quick reply

elamranii SSC-Addicted Points: 445 More actions · Answer 5

Hi All,

I used this one which almost worked.

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

Pre Lab Val------------------------Post Lab Val -------------------a --------B

PLT=48=03/02/2016 10:56;------PLT=80=03/02/2016 20:45;----------- 48= -------80=

PLT=48=03/02/2016 10:56;------PLT=80=03/02/2016 20:45;---------- 48= --------80=

I added column a and b and put the above formula but showing the equal sign

pietlinden SSC Guru Points: 63428 More actions · Answer 6

This worked with the data you posted:

CDBL(MID(Fields!TwoFields.Value,INSTR(Fields!TwoFields.Value,"=")+1,INSTRREV(Fields!TwoFields.Value,"=") - INSTR(Fields!TwoFields.Value,"=")+1))

elamranii SSC-Addicted Points: 445 More actions · Answer 7

=CDBL(MID(Fields!PRE_LAB_VAL.Value,INSTR(Fields!PRE_LAB_VAL.Value,"=")+1,INSTRREV(Fields!PRE_LAB_VAL.Value,"=") - INSTR(Fields!PRE_LAB_VAL.Value,"=")+1))

I use it but getting an error

pietlinden SSC Guru Points: 63428 More actions · Answer 8

I created two fields in my dataset

"TwoFields" is the name of the field containing the raw data

FirstDelim = INSTR(Fields!TwoFields.Value,"=")+1

SecondDelim = =INSTRREV(Fields!TwoFields.Value,"=")

=CDBL(MID(Fields!TwoFields.Value,Fields!FirstDelim.Value,Fields!SecondDelim.Value-Fields!FirstDelim.Value))

If you can, I would probably do as Alan suggested and do the string manipulation in T-SQL.

elamranii SSC-Addicted Points: 445 More actions · Answer 9

I am sorry I am new and this is not clear to me.

I added a column and inserted the formula. Your looks like 3 columns. FirstDelim, seconddelim and the final one.

I am not following how to do this. thanks for being patient.

pietlinden SSC Guru Points: 63428 More actions · Answer 10

FirstDelim and SecondDelim are just (intermediate) Calculated Fields in my SSRS dataset. The result you're interested in is the third one that use FirstDelim and SecondDelim. You could just substitute... it's all just basic algebra, but I figured it would be easier to see that way.

elamranii SSC-Addicted Points: 445 More actions · Answer 11

I put this but still getting an error:

CDBL(MID(Fields!PRE_LAB_VAL.Value,INSTR(Fields!PRE_LAB_VAL.Value,"=")+1,INSTRREV(Fields!PRE_LAB_VAL.Value,"=") - INSTR(Fields!PRE_LAB_VAL.Value,"=")+1))

Thank you.

MMartin1 One Orange Chip Points: 27768 More actions · Answer 12

elamranii (3/7/2016)
I put this but still getting an error:
CDBL(MID(Fields!PRE_LAB_VAL.Value,INSTR(Fields!PRE_LAB_VAL.Value,"=")+1,INSTRREV(Fields!PRE_LAB_VAL.Value,"=") - INSTR(Fields!PRE_LAB_VAL.Value,"=")+1))
Thank you.

First , just to explain >

CDBL(

MID(

Fields!PRE_LAB_VAL.Value,/* column to look in */

INSTR(Fields!PRE_LAB_VAL.Value,"=")+1,/* position in the string to begin at */

INSTRREV(Fields!PRE_LAB_VAL.Value,"=") - INSTR(Fields!PRE_LAB_VAL.Value,"=")+1 /* how many characters (including the start position) to get */

)

However, there may be an issue with that formula... try to put an extra set of parenthesis around the second INSTR function and the +1. So you will end up with

CDBL(

MID(

Fields!PRE_LAB_VAL.Value,

INSTR(Fields!PRE_LAB_VAL.Value,"=")+1,

INSTRREV(Fields!PRE_LAB_VAL.Value,"=") - (INSTR(Fields!PRE_LAB_VAL.Value,"=")+1 )

)

See if this gives the results you want.

----------------------------------------------------

elamranii SSC-Addicted Points: 445 More actions · Answer 13

=CDBL(MID(Fields!PRE_LAB_VAL.Value,INSTR(Fields!PRE_LAB_VAL.Value,"=")+1,INSTRREV(Fields!PRE_LAB_VAL.Value,"=") - (INSTR(Fields!PRE_LAB_VAL.Value,"=")+1 )))

This almost worked.

the data is not consistent in the report.

for this it works

PLT=48=etc...........................I get 48

PLT-241=etc..........................I get 241

but

PT=45.7=etc.........................I get #error

PT=17.9=etc.........................I get #error

I hope this make sense. Thank you so much for your help.

MMartin1 One Orange Chip Points: 27768 More actions · Answer 14

From here what you should do is is create columns in the report (just to audit) that list the return values of these functions to see what actually got returned from the INSTR and so on. From here you can do the math on your own to get the result string yourself where you see the errors. See if there are any issues. From what I can see this should work, though.

----------------------------------------------------