SSRS Extract substring from string before the second string

Question

I have a field which is like the following 4444-4444-TestingItem1. Is it possible for me to extract the field up until 4444-4444. if so how would I do this? What function would I use within SSRS. Please note It could be any number of characters before the first dash and before the second dash.

How can this be achieved within SSRS?

Solution 1

Tim and Alejandro both give good answers that work with your example. This is a combination of the two, as an SSRS expression rather than SQL, and handles strings with more than two dashes.

=LEFT(Fields!FIELD.Value, InStr(InStr(Fields!FIELD.Value, "-") +1, Fields!FIELD.Value, "-") -1)

Solution 2

In SSRS you can use LEFT and InStrRev functions:

=LEFT(Fields!YourField.Value,InStrRev(Fields!YourField.Value,"-")-1)

Let me know if this helps.

Solution 3

You can do this using a combination of CHARINDEX and SUBSTRING:

SELECT SUBSTRING(field, 1, CHARINDEX('-', field, CHARINDEX('-', field)+1) - 1)
FROM yourTable

Solution 4

pat1 : Find first position of char is non-numeric or "-"

pat2 : find first position of "-" and follow non-numeric

    SELECT a.s,LEFT(a.s,PATINDEX('%[^0-9-]%',a.s)-1) AS pat1
              ,LEFT(a.s,PATINDEX('%[-][^0-9]%',a.s)-1)  AS pat2
    FROM (VALUES('4444-4444-TestingItem1')) a(s)

s                      pat1                   pat2
---------------------- ---------------------- ----------------------
4444-4444-TestingItem1 4444-4444-             4444-4444

View more solutions

Comments

[rjose]

This question was about SSRS, and the answer is for SQL. Am I missing something?