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

LVL 1
cheryl9063Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief 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
 
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
 
cheryl9063Author Commented:
Thanks!
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.