Extract 5 characters to the R/L once criteria met?

Transact-SQL
1

I would like to search in [description] to find the number 40, once located. Return the first 5 characters to the Right and Left and place them in respective LeftTest / RightTest columns.

update SQLAGENDA
set LeftTest = left(description,  5)
where description like '% 40 %' or text like '% $40 %' or text like '% $40.00 %'
select * from SQLAGENDA

all I have so far and researching from here.

2

Try this:

update sqlagenda
   set lefttest=case
                   when charindex(' 40 ',[description])>0
                   then right(substring([description],0,charindex(' 40 ',[description])),5)
                   when charindex(' $40 ',[description])>0
                   then right(substring([description],0,charindex(' $40 ',[description])),5)
                   when charindex(' $40.00 ',[description])>0
                   then right(substring([description],0,charindex(' $40.00 ',[description])),5)
                end
      ,righttest=case
                    when charindex(' 40 ',[description])>0
                    then left(substring([description],charindex(' 40 ',[description])+len(' 40 '),5),5)
                    when charindex(' $40 ',[description])>0
                    then left(substring([description],charindex(' $40 ',[description])+len(' $40 '),5),5)
                    when charindex(' $40.00 ',[description])>0
                    then left(substring([description],charindex(' $40.00 ',[description])+len(' $40.00 '),5),5)
                 end
 where [description] like '% 40 %'
    or [description] like '% $40 %'
    or [description] like '% $40.00 %'
;
1 Like