• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 775
  • Last Modified:

Parse everything to Right of slash or to the left

I have a query field [BEN-COV-OPTION] with values like
$15000/$7500
$5000/$2500

I need to split this out into two fields.  I need an expression that will return everything to the left of the slash mark regardless of how many characters it may be and I need an expression that will return everything to the right of the slask mark regardless of how many characters it may be.  Any help would be most appreciated.
0
jph826
Asked:
jph826
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
'Slash character position
?InStr(1, "$5000/$2500", "/")
 6

'Everything left of Slash
?Left("$5000/$2500", InStr(1, "$5000/$2500", "/") - 1)
$5000

'Everything right of Slash
?Right("$5000/$2500", Len("$5000/$2500") - InStr(1, "$5000/$2500", "/"))
$2500
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
0
 
Patrick MatthewsCommented:
Try:

SELECT [BEN-COV-OPTION],
    Left([BEN-COV-OPTION], InStr(1, [BEN-COV-OPTION] & "/", "/") - 1) AS xLeft,
    Mid([BEN-COV-OPTION], InStr(1, [BEN-COV-OPTION] & "/", "/") + 1) AS xRight
FROM [SomeTable]

Open in new window


In that, if there is no "/" character, then the whole thing goes to xLeft, and xRight comes back as a zero length string.

jimhorn's suggestions above will return an error for the "left" is there is no slash, and will put everything into the "right" if there is no slash.  That might or might not be closer to what you want :)
0
 
jph826Author Commented:
Thank you both for responding so quickly!  

Thank you matthewspatrick, that worked perfectly.  I forgot to state that the [BEN-COV-OPTION] field could also be blank, and for those I was getting "#Error".  You had the foresight to consider that as well.  Thanks again!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now