Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Extracting Characters/Numbers from a string

Extracting Characters/Numbers from a string

Extracting Characters/Numbers from a string

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!

RE: Extracting Characters/Numbers from a string

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.

RE: Extracting Characters/Numbers from a string

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

RE: Extracting Characters/Numbers from a string

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.

RE: Extracting Characters/Numbers from a string

This is great!!! Thank you so much! I think with this I may be able to figure out the last two.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close