Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Extracting Characters/Numbers from a string 1

Status
Not open for further replies.

SandeA

Technical User
Sep 5, 2019
5
US
I'm fairly new to SSRS and still learning what all the many functions will do. I have a string that contains the entire legal description of a location (Township, Range, Section, and Subsection). An example is: T36SR01W36NESE. This would be easy to use the LEFT function but the numbers between the T & S and between the R & W aren't always padded with the leading 0. In addition, the last 4 showing NESE are sometimes only two characters. What I'm trying to figure out is how to get them to be 4 separate fields:
Township: T36S
Range: R01W
Section: 36
SubSection: NESE

Township will always begin with T and end with S or N
Range will always begin with R end with E or W
Section is always 2 digit (although I'm not sure the lower numbers have the leading 0 all the time)

Is it possible to create functions to extract this data?

Thank you in advance for any help and advice!
Sande
 
I was trying to make an example with using the InStr and MID functions (using Report Builder and Excel as a data source). But it is giving me issues.
 
I was trying to use the InStr but it just kept returning a 1 or 0 instead of the actual value. And I'm trying to do this using Expressions for each of the fields on the 4 fields on the report. I'm not sure it can be done. [sad]
 
I can be done, I was able to extract the first two string pretty easy.

Here is a example of what I did for the two strings (I could have made variables for the InStr values). The next two strings get a lot more complicated (need to do a loop to parse through the characters).

=mid(First(Fields!Parcel.Value, "DataSet1"),
instr(first(Fields!Parcel.Value, "DataSet1"),"T"),
iif(instr(first(Fields!Parcel.Value, "DataSet1"),"S") > 0,
instr(first(Fields!Parcel.Value, "DataSet1"),"S"),
instr(first(Fields!Parcel.Value, "DataSet1"),"N")))

=mid(First(Fields!Parcel.Value, "DataSet1"),
instr(first(Fields!Parcel.Value, "DataSet1"),"R"),
iif(instr(first(Fields!Parcel.Value, "DataSet1"),"E") > 0,
len(First(Fields!Parcel.Value, "DataSet1"))-instr(first(Fields!Parcel.Value, "DataSet1"),"E")+2,
len(First(Fields!Parcel.Value, "DataSet1"))-instr(first(Fields!Parcel.Value, "DataSet1"),"W")+2))

Unfortunately I do not have time to work on the other two strings. Maybe someone else can figure out the code.

 
This is great!!! Thank you so much! I think with this I may be able to figure out the last two.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top