T-SQL string help

We have a horrible table that I have adopted where the data type is nvarchar.. The data is supposed to look like 99 in the Grade2 column and most of it does but some records have $99,$110,$299.. ALL THREE plus $.. So basically this is what I want to do.. IF the record has $99,$110,$299 in Grade2  I want to put the 99 in a new column called Grade1 and leave 110 in the column called Grade2 and drop the third value $299.. I only want to do this if the column called Desc is not null.. I know this will be a Case Select I think? I have attached a spreadsheet as an example.. Tab1 shows what it currently looks like and Tab2 shows what I want. How do I do this?
Example.xls
LVL 1
cheryl9063Asked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
or where you wanting this?

update yourtable
set grade1=substring(grade2,2,charindex(',',grade2)-2)
  ,grade2=substring(grade2,charindex(',',grade2)+3,charindex(',',grade2,charindex(',',grade2,2)+1)-charindex(',',grade2)-2)
where grade2 like '$%,$%,%'
and desc is not null
0
 
LowfatspreadCommented:
update yourtable
set grade1=99
    ,grade2=110
where grade2 ='$99,$110,$299'
AND DESC IS NOT NULL
0
 
LowfatspreadCommented:
SORRY

update yourtable
set grade1='99'
    ,grade2='110'
where grade2 ='$99,$110,$299'
AND DESC IS NOT NULL
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.