Right function starting at specific character

i have the following field that varies in lenght and need to do a right function based on everything after the "-"

example of fields:
123456-knoxville
85632547-charlotte
22222-Chattanooga


want it to return:
knoxville
charlotte
Chattanooga
kelsanitAsked:
Who is Participating?
 
mlmccCommented:
Assuming there is always a - in the text use

Split({@EquipmentID},'-')[1]

mlmcc
0
 
Rgonzo1971Commented:
Hi,

pls try

MID({myField}, INSTR({myField }, " ") + 1, LEN({myField})-( INSTR({myField }, " ") + 1))

Open in new window


Regards
0
 
kelsanitAuthor Commented:
here is what i entered as "myfiedld" is  {@EquipmentID}
MID({@EquipmentID}, INSTR({@EquipmentID}, " ") + 1, LEN({@EquipmentID}-(INSTR({@EquipmentID}, " ") + 1)))

Get error " A number, currency amount, date, time or date time is required
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
mlmccCommented:
You have the ( ) paired in the wrong places.  Try that this way

MID({@EquipmentID}, INSTR({@EquipmentID}, " ") + 1, LEN({@EquipmentID}) - INSTR({@EquipmentID}, " ") + 1)

mlmcc
0
 
kelsanitAuthor Commented:
Tried the first fuction SPLIT and got the following error "A subscript must be between 1 and the size of the array"
Split({@EquipmentID},'-')[1]

Tried the other MID function and returned the full text.
MID({@EquipmentID}, INSTR({@EquipmentID}, " ") + 1, LEN({@EquipmentID}) - INSTR({@EquipmentID}, " ") + 1)
0
 
mlmccCommented:
THe INSTR needs to search for  -

MID({@EquipmentID}, INSTR({@EquipmentID}, "-") + 1, LEN({@EquipmentID}) - INSTR({@EquipmentID}, "-") + 1)

the subscript error indicates some don't have a - or are NULL

If InStr({@EquipmentID}, "-")  > 0 then
     Split({@EquipmentID},'-')[2]
Else
     {@EquipmentID}

Open in new window


What is the {@EquipmentID} formula?

mlmcc
0
 
kelsanitAuthor Commented:
if {RS_ROUTE_EQUIPMENT.EQUIPMENT_ID}="-" then "" else {RS_ROUTE_EQUIPMENT.EQUIPMENT_ID}
0
 
kelsanitAuthor Commented:
Used the equipmentid field in the table and the split function worked.  Thank you
0
 
kelsanitAuthor Commented:
found one more issue.

there are sometimes two "-" in the text field how do I make sure i always get the text after the last dash?

example:

487-156-knoxville
12345-knoxville

desired output:
knoxville
knoxville
0
 
mlmccCommented:
Try this with the split

Split({RS_ROUTE_EQUIPMENT.EQUIPMENT_ID},'-')[UBound(Split({RS_ROUTE_EQUIPMENT.EQUIPMENT_ID},'-')]

mlmcc
0
 
James0628Commented:
FWIW, another way to get everything after the last "-" would be:

Mid ({RS_ROUTE_EQUIPMENT.EQUIPMENT_ID}, InStrRev ({RS_ROUTE_EQUIPMENT.EQUIPMENT_ID}, "-") + 1)

 If the field could be null, you could add a check for that.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.