Click here to Skip to main content
15,905,148 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have text as "AM.JUBAIL.PRE-FINISHING-LINE.PFBSTR.PFBST1" where i need to trim and display only last word in that text as PFBST1.

I need to know which expression or condition need to use eliminate rest of words.

What I have tried:

=Trim(Fields!LastEquipment.Value,"AM")
Posted
Updated 14-Nov-18 2:37am

1 solution

TRIM removes whitespace. You want a SUBSTRING of the text.

Try this
SQL
declare @text varchar(50) = 'AM.JUBAIL.PRE-FINISHING-LINE.PFBSTR.PFBST1'
select reverse(left(reverse(@text), charindex('.',reverse(@text)) - 1))

Here is the reference material
LEFT (Transact-SQL) | Microsoft Docs[^]
REVERSE (Transact-SQL) | Microsoft Docs[^]
CHARINDEX (Transact-SQL) | Microsoft Docs[^]

If you look at the results of these queries you can see more easily what is going on ...
select reverse(@text)
select charindex('.',reverse(@text))
select left(reverse(@text), charindex('.',reverse(@text)) - 1)
Results ...
1TSBFP.RTSBFP.ENIL-GNIHSINIF-ERP.LIABUJ.MA
7
1TSBFP


[EDIT] Apologies - this was quite clearly tagged SSRS and I provided a SQL solution. Try the following instead (caveat - untested)
= Mid(Fields!LastEquipment.Value, InstrRev(Fields!LastEquipment.Value, ".") + 1, Length(Fields!LastEquipment.Value))
 
Share this answer
 
v3
Comments
Member 14053897 14-Nov-18 8:47am    
These query is not working because i need to use in Report builder not in SQl query. is there any way to use a expression in report builder.
CHill60 14-Nov-18 10:29am    
I've updated my solution. Sorry about that.
Member 14053897 17-Nov-18 1:27am    
I am getting error for this condition as "bc30451: is not declared. it may be inaccessible due to its protection level."
CHill60 22-Nov-18 7:40am    
It could mean that Fields!LastEquipment has gone out of scope. Does it work if you just use
=Fields!LastEquipment.Value

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900