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

T-SQL Help

I need to pull the last value from the field.. How do i do this? All three numbers are in the same field. The data type is nvarchar.. I want to pull out the 1799.. NO $ just the 1799.. Some of the fields could have $800 and I want only 800.. I know the code below works for the first value(599) but how do I extract the last?

$599,$660,$1799
substring(grade2,2,charindex(',',grade2)-2)

Open in new window

0
cheryl9063
Asked:
cheryl9063
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
If you want to use that code, you can just reverse the string and check for the first CHARINDEX() and pull RIGHT() of the string that many characters minus 1 for the ',' itself. Other approaches might include a split string function, but given you are comfortable with what you have and the structure, you could stick with that minor adjustment.
0
 
Kevin CrossChief Technology OfficerCommented:
Here is an example:
DECLARE @grade2 VARCHAR(25);
SET @grade2 = '$599,$660,$1799';
SELECT RIGHT(@grade2, CHARINDEX(',', REVERSE(@grade2))-2)
     , CONVERT(INT, CONVERT(MONEY, RIGHT(@grade2, CHARINDEX(',', REVERSE(@grade2))-1)));

Open in new window


If there will always be ',$', then -2 works just fine. If the '$' may or may not exist, you can try something similar to what I showed -- adjusting as needed. It converts to MONEY which handles the '$' in conversion and then convert back to INT or if decimal places allowed DECIMAL|NUMERIC.

Hope that helps!
0
 
cheryl9063Author Commented:
Thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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